Antonio - 1 month ago 4x
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?

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
``````
Source (Stackoverflow)