Antonio Antonio - 2 months ago 5
SQL Question

Count who paid group by 1, 2 or 3+

I have a payment table like the example below and I need a query that gives me how many IDs payd (AMOUNT > 0) 1 time, 2 times, 3 or more times. Exemple:


+----+--------+
| ID | AMOUNT |
+----+--------+
| 1 | 50 |
| 1 | 0 |
| 2 | 10 |
| 2 | 20 |
| 2 | 15 |
| 2 | 10 |
| 3 | 80 |
+----+--------+


I expect the result:


+-----------+------------+-------------+
| 1 payment | 2 payments | 3+ payments |
+-----------+------------+-------------+
| 2 | 0 | 1 |
+-----------+------------+-------------+


I'm doing manually on excel right now but I'm pretty sure there is a more pratical solution. Any ideas?

Answer

A little sub-query will do the trick

Declare @YOurTable table (ID int, AMOUNT int)
Insert into @YourTable values
( 1 ,     50 ),
(  1 ,      0) ,
(  2 ,     10) ,
(  2 ,     20) ,
(  2 ,     15) ,
(  2 ,     10) ,
(  3 ,     80) 


Select [1_Payment] = sum(case when Cnt=1 then 1 else 0 end)
      ,[2_Payment] = sum(case when Cnt=2 then 1 else 0 end)
      ,[3_Payment] = sum(case when Cnt>2 then 1 else 0 end)
 From (
        Select id
              ,Cnt=count(*) 
         From  @YourTable 
         Where Amount<>0 
         Group By ID
      ) A

Returns

1_Payment   2_Payment   3_Payment
2           0           1