Robert Broden Robert Broden - 3 months ago 11
SQL Question

SQL report: count total rows per user and count successful transactions per user

We are using a Firebird 2.1 database and trying to run a query for a report that counts total transactions and transactions of a certain status. Each transaction has a status that is either successful (1) or unsuccessful (0). There was also a denied status (2) but that's covered in the comments. 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. Where TOTAL_FRUIT counts the total number of transactions and EATEN_FRUIT counts the total of successful transactions based on 0 or 1

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


Thank you. Your help is much appreciated. Our "best" attempt at an SQL statement for this was:

WITH alias1 AS
(SELECT USER_ID, count(Fruit) as TOTAL_FRUIT FROM myTable GROUP BY USER_ID),
alias2 AS
(SELECT USER_ID, count(Fruit) as EATEN_FRUIT FROM myTable WHERE Eaten=1 GROUP BY USER_ID)
SELECT alias2.USER_ID, TOTAL_FRUIT, EATEN_FRUIT
FROM alias1, alias2


However, both datasets alone work fine but, when processed together, the first dataset is only the first row over and over again.

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
Comments