SQL Server sum function

Question

i have the following Problem:

A Table with Bills contains for example three Columns Hospital , Bill-Number and Bill-Amount.

---------------------------------
| hospital | Number  | Amount   |
---------------------------------
|  a       | 33    | 20         |
---------------------------------
|  a       | 11    | 10         |
---------------------------------
|  a       | 5     | 40         |
---------------------------------
|  a       | 34    | 55         |
---------------------------------
|  b       | 3     | 20         |
---------------------------------
|  b       | 9     | 30         |
---------------------------------
|  b       | 11    | 15         |
---------------------------------

The Bill-Numbers have range like from 0-9 and from 10-19 and from 20-29 and from 30-39 So I need a Result-Table that shows me the Hospital and the Total Amount and all the Bills that belongs to the same Range together like :

----------------------------------------------
|Hospital | range1|range2|range3|range4|Total|
----------------------------------------------
|a        | 5     |11    |0     |67    |93   |
----------------------------------------------
|b        | 12    | 11   | 0    | 0    |23   |
----------------------------------------------

I could easily get the hospital and the total ( normal group by and sum function ) but when i try to get this ranges thing it doesnt work.

Could anyone help me with this point?

Answer 1

You can use conditional aggregation:

select hospital,
       sum(case when number between 0 and 9 then amount else 0 end) as range1,
       . . .
from bills b
group by hospital;