mercredi 4 février 2015

SUM values within a grouped table


[MSSQL] Having a table like:



---------------------------------
| A | B | C | D |
---------------------------------
| 1 | ABC | 1.3 | 2.5 |
| 1 | ABC | 2.7 | 2.5 |
| 1 | ABC | 0.5 | 2.5 |
| 1 | ABC | 4.2 | 2.5 |
| 2 | CAB | 6.3 | 5.7 |
| 2 | CAB | 1.9 | 5.7 |
| 3 | BCA | 7.7 | 2.5 |
| 3 | BCA | 5.8 | 2.5 |
| 3 | BCA | 2.1 | 2.5 |
| 4 | CBA | 0.3 | 5.7 |
| 4 | CBA | 8.2 | 5.7 |
---------------------------------


What would be the best approach to obtaining a resultset like:



-----------------------------------------
| c1 | c2 | c3 | c4 | c5 |
-----------------------------------------
| 1 | ABC | 8.7 | 2.5 | 11.2 |
| 2 | CAB | 8.2 | 5.7 | 13.9 |
| 3 | BCA | 13.5 | 2.5 | 16 |
| 4 | CBA | 8.5 | 5.7 | 14.2 |
-----------------------------------------


Where, t1 is grouped A, t2 is grouped B, c3 is the SUM of C, c4 is grouped D and c5 is c3+c4


So as you can see A, B and D are fixed values being repeted by the JOIN that generates the table, Ideally this values would be grouped and the values in C will SUM.


I refer myself to this question but didn't help much.


Any ideas are appreciated.





Aucun commentaire:

Enregistrer un commentaire