SQL RollUp Cube get Totals and Sub Totals of groups

The cube and rollup are extensions of the group by clause.

https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

It allows to return the totals and sub totals of groups.

Cube returns all combinations from the groups specified while RollUp returns unique combinations from the groups specified.


DECLARE @PortfolioProgramme AS TABLE (
Portfolio VARCHAR(100),
Programme VARCHAR(100),
Project VARCHAR(100),
[Cost (in Thousands)] INT
)

INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme1','Project1',9 )
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme1','Project11',8 )
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme1','Project2',5.5)
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme1','Project3',7.5)
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme2','Project4',9.5)
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme2','Project5',2.5)
INSERT INTO @PortfolioProgramme VALUES('P001', 'Programme2','Project6',1.5)
INSERT INTO @PortfolioProgramme VALUES('P002', 'Programme3','Project7',30)
INSERT INTO @PortfolioProgramme VALUES('P002', 'Programme3','Project8',20)
INSERT INTO @PortfolioProgramme VALUES('P002', 'Programme4','Project9',11 )
INSERT INTO @PortfolioProgramme VALUES('P002', 'Programme4','Project10',6.5)


SELECT Portfolio,Programme,Project,
SUM ([Cost (in Thousands)]) AS [Cost (in Thousands)]
FROM @PortfolioProgramme
GROUP BY ROLLUP(Portfolio,Programme,Project)

Results

Portfolio Programme Project Cost (in Thousands)
P001 Programme1 Project1 9
P001 Programme1 Project11 8
P001 Programme1 Project2 5
P001 Programme1 Project3 7
P001 Programme1 NULL 29
P001 Programme2 Project4 9
P001 Programme2 Project5 2
P001 Programme2 Project6 1
P001 Programme2 NULL 12
P001 NULL NULL 41
P002 Programme3 Project7 30
P002 Programme3 Project8 20
P002 Programme3 NULL 50
P002 Programme4 Project10 6
P002 Programme4 Project9 11
P002 Programme4 NULL 17
P002 NULL NULL 67
NULL NULL NULL 108

SELECT Portfolio,Programme,Project,
SUM ([Cost (in Thousands)]) AS [Cost (in Thousands)]
FROM @PortfolioProgramme
GROUP BY Cube(Portfolio,Programme,Project)

Results

Portfolio Programme Project Cost (in Thousands)
P001 Programme1 Project1 9
NULL Programme1 Project1 9
NULL NULL Project1 9
P002 Programme4 Project10 6
NULL Programme4 Project10 6
NULL NULL Project10 6
P001 Programme1 Project11 8
NULL Programme1 Project11 8
NULL NULL Project11 8
P001 Programme1 Project2 5
NULL Programme1 Project2 5
NULL NULL Project2 5
P001 Programme1 Project3 7
NULL Programme1 Project3 7
NULL NULL Project3 7
P001 Programme2 Project4 9
NULL Programme2 Project4 9
NULL NULL Project4 9
P001 Programme2 Project5 2
NULL Programme2 Project5 2
NULL NULL Project5 2
P001 Programme2 Project6 1
NULL Programme2 Project6 1
NULL NULL Project6 1
P002 Programme3 Project7 30
NULL Programme3 Project7 30
NULL NULL Project7 30
P002 Programme3 Project8 20
NULL Programme3 Project8 20
NULL NULL Project8 20
P002 Programme4 Project9 11
NULL Programme4 Project9 11
NULL NULL Project9 11
NULL NULL NULL 108
P001 NULL Project1 9
P001 NULL Project11 8
P001 NULL Project2 5
P001 NULL Project3 7
P001 NULL Project4 9
P001 NULL Project5 2
P001 NULL Project6 1
P001 NULL NULL 41
P002 NULL Project10 6
P002 NULL Project7 30
P002 NULL Project8 20
P002 NULL Project9 11
P002 NULL NULL 67
P001 Programme1 NULL 29
NULL Programme1 NULL 29
P001 Programme2 NULL 12
NULL Programme2 NULL 12
P002 Programme3 NULL 50
NULL Programme3 NULL 50
P002 Programme4 NULL 17
NULL Programme4 NULL 17

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s