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?

Possible duplicate of [In SQL, how can you "group by" in ranges?](https://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges) — Tanner, Jan 17 '18 at 14:22

score 1 · Accepted Answer · answered Jan 17 '18 at 13:46

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;

answered Jan 17 '18 at 13:46

Gordon Linoff

1,242,037
58
646
786

But that will not give me the sum of all Bills in one Range right ? – memo_star Jan 17 '18 at 13:48
Yikes, using `BETWEEN` hurts my eyes ;) I prefer `>= 0 AND < 10` – MatBailie Jan 17 '18 at 13:50
@memo_star The example given aggregates only the amounts for rows where number is between 0 and 9. The `. . .` is to imply that you can do the same with different upper and lower bounds in another field, using `range1` as a template. If you don't trust what it does *(and you never really should just Trust anything)* then just test it? – MatBailie Jan 17 '18 at 13:51

SQL Server sum function

1 Answers1