Cube & rollup

Transact-SQL
1

Hi ,

How can i implement those 2 function in a simple SQL query ? i have a database that done support this 2 functions ,

input -

class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

rollup example

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with ROLLUP

 Output:
 class	section	sum
 A	a	230
 A	b	230
 A	NULL	460  -- 230 + 230  = 460
 B	a	190
 B	b	210
 B	NULL	400  -- 190 + 210 = 400
 NULL	NULL	860  -- 460 + 400 = 860 


cube exmaple -

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with CUBE

 Output:
 class	section	sum
 A	a	230
 A	b	230
 A	NULL	460  -- 230 + 230  = 460
 B	a	190
 B	b	210
 B	NULL	400  -- 190 + 210 = 400
 NULL	NULL	860  -- 460 + 400 = 860
 NULL	a	420  -- 230 + 190 = 420
 NULL	b	440  -- 230 + 210 = 440
2

Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

3

You can wrap the original query in a cte and use the cte to sum it different ways. For your ROLLUP example, it would be something like shown below. If you want the results to show in specific order, you will need to add an order by clause. Sometimes, you may need to add an additional column to the final 3 selects to indicate which set a result came from so you can use that for ordering.

;WITH cte AS
(
	SELECT  class ,
			section ,
			SUM(marks) [sum]
	FROM    #tempTable
	GROUP BY class ,
			section
)
SELECT * FROM cte 

UNION ALL

SELECT class, NULL ,SUM([sum])
FROM cte GROUP BY class

UNION ALL

SELECT NULL, NULL, SUM([sum])
FROM cte;
1 Like