Robert Broden Robert Broden - 2 months ago 7x
SQL Question

SQL report to count total and WHERE condition

We are using a Firebird 2.1 database and trying to run a query for a report. I'll use ambiguous data.

USER_ID, Fruit, Eaten
1, Apple, 0
1, Banana, 1
1, Kiwi, 1
2, Apple, 1
2, Banana, 1
3, Apple, 0

What we would like to do is run a report that shows USER_ID, TOTAL_FRUIT, and EATEN_FRUIT

1, 3, 2
2, 2, 2
3, 1, 0

Thank you. Your help is much appreciated.


You should use count(*), sum() and group by

select user_id, count(*), sum(eaten)
from my_table 
group by user_id

you can eventually also manage for null value

select user_id, count(*), sum(coalesce(eaten,0))
from my_table 
group by user_id