Robert Broden Robert Broden - 3 months ago 24
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

USER_ID, TOTAL_FRUIT, EATEN_FRUIT
---------------------------------
1, 3, 2
2, 2, 2
3, 1, 0


Thank you. Your help is much appreciated.

Answer

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