vendredi 31 juillet 2015

how to get sum of each column new records in sql server

Hi Friends I have small doubt in sql server please tell me how to achive this task in sql server

productname |Level|  January  | Feburary | March | total
  Rin       | L1  |   10      | 20       |  30   | 60
  Rin       | L2  |   5       | 10       |  10   | 25
  Rin       | L3  |  20       | 5        |  5    | 30
  Pen       | L1  |  5        | 6        |  10   | 21 
  Pen       | L2  |  10       | 10       | 20    | 40
  Pen       | L3  | 30        |10        | 40    | 80

based on above table data I want output like below

productname |Level|  January  | Feburary | March | total
  Rin       | L1  |   10      | 20       |  30   | 60
  Rin       | L2  |   5       | 10       |  10   | 25
  Rin       | L3  |  20       | 5        |  5    | 30
RinTotal    |All  |  35       |35        |  45   |115
  Pen       | L1  |  5        | 6        |  10   | 21 
  Pen       | L2  |  10       | 10       | 20    | 40
  Pen       | L3  | 30        |10        | 40    | 80
PenTotal   | All  | 45        |26        | 70    |141

I tried like bellow query

select productname,level,sum(january)as January,sum(Feburary)as Feburary),Sum(march)as March,Sum(total)as total
from test
union
select * from test 

but its not given exact output .please tell me query how to achive this task in sql server.

Aucun commentaire:

Enregistrer un commentaire