## 3 replies

Last post Feb 18, 2015 03:36 PM by limno

Member

65 Points

326 Posts

### How to Get Oupt Like

Hi,

Below is the My Table data...

```Type	User Name	region	   duration1	duration2	dails	Math	Silver	Gold
A	Ramesh	      Section01	     892	  1742	          40	 1	 3	103
B	Naresh	      Section01	     0	           0	           0	 0	 0	8
B	Singh	      Section01	     0	          3003	          120	 0	 2	26
B	Kanth	      Section01	    359	          1261	          52	 1	 0	13
B	Peter	      Section01	    1716	  3230	          173	 0	 1	28
Output should be like below:```
```Type	User Name	region	   duration1	duration2	dails	Math	Silver	Gold
A	Ramesh	      Section01	     892	  1742	          40	 1	 3	103
B	Naresh	      Section01	     0	           0	           0	 0	 0	8
B	Singh	      Section01	     0	          3003	          120	 0	 2	26
B	Kanth	      Section01	    359	          1261	          52	 1	 0	13
B	Peter	      Section01	    1716	  3230	          173	 0	 1	28
Total		   Section01-Total  2967	  9236	          385	 1	 3	103```
`Above From Duration1(Calculating type of A+B),Duration2(Calculating type of A+B),Dials(Calculating Type of A+B)But Math,Silver,Gold I need to Calculate of Type A.I need to get The Total Like above out put.Help me,Thanks `

skg

Contributor

5981 Points

1413 Posts

### Re: How to Get Oupt Like

Hello sureshtalla,

```with cte as (
select *
from mytable)
select * from cte
union all
select '','',region + '-total',sum(duration1),sum(duration2),sum(dails),sum(math),sum(silver),sum(gold)
from cte
group by region```

I thought of using Cube or Group By Grouping Sets, etc but above query will be enough I guess
You can also check those

Member

65 Points

326 Posts

### Re: How to Get Oupt Like

But it's not working.

Thanks

skg
• ### limno

All-Star

122148 Points

9670 Posts

Moderator

### Re: How to Get Oupt Like

```select * from test1

;with cte as (
select *
from test1)
select * from cte
union all
select '','',region + '-total',sum(duration1),sum(duration2),sum(dails)
,sum(Case when type='A' Then math Else 0 End)
,sum(Case when type='A' Then silver Else 0 End)
,sum(Case when type='A' Then gold Else 0 End)
from cte
group by region```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm