Antonio - 8 months ago 25

SQL Question

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
```