GROUPING SETS example
-
Sunday, December 30, 2012 2:11 AM
I am using SQL Server 2012 Standard Edition, and I was trying to learn about GROUPING SETS in Craig Freedman's SQL Server Blog (http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx). I made a table called "SalesRevised" to test his last example. The table has the following data:
EmpId Month Yr Sales
1 3 2005 12000.00
1 7 2006 18000.00
1 4 2007 25000.00
2 12 2005 15000.00
2 8 2006 6000.00
3 9 2006 20000.00
3 6 2007 24000.00In his last example, he shows the following code (I had to change the name of "Sales" to "SalesRevised"):
SELECT EmpId, Month, Yr, SUM(Sales) AS SalesRevised FROM SalesRevised GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month)))
The result set of the above query is shown below:
EmpId Month Yr SalesRevised
1 3 2005 12000.00
1 NULL 2005 12000.00
1 7 2006 18000.00
1 NULL 2006 18000.00
1 4 2007 25000.00
1 NULL 2007 25000.00
1 NULL NULL 55000.00
2 12 2005 15000.00
2 NULL 2005 15000.00
2 8 2006 6000.00
2 NULL 2006 6000.00
2 NULL NULL 21000.00
3 9 2006 20000.00
3 NULL 2006 20000.00
3 6 2007 24000.00
3 NULL 2007 24000.00
3 NULL NULL 44000.00I wanted to find out what would happen if I added "Yr" between "EmpID" and "ROLLUP(Yr, Month)", so the code would be changed to the following:
SELECT EmpId, Month, Yr, SUM(Sales) AS SalesRevised FROM SalesRevised GROUP BY GROUPING SETS((EmpId, Yr, ROLLUP(Yr, Month)))
The result set of the above query is shown below:
EmpId Month Yr SalesRevised
1 3 2005 12000.00
1 NULL 2005 12000.00
1 7 2006 18000.00
1 NULL 2006 18000.00
1 4 2007 25000.00
1 NULL 2007 25000.00
2 12 2005 15000.00
2 NULL 2005 15000.00
2 8 2006 6000.00
2 NULL 2006 6000.00
3 9 2006 20000.00
3 NULL 2006 20000.00
3 6 2007 24000.00
3 NULL 2007 24000.00
1 NULL 2005 12000.00
2 NULL 2005 15000.00
1 NULL 2006 18000.00
2 NULL 2006 6000.00
3 NULL 2006 20000.00
1 NULL 2007 25000.00
3 NULL 2007 24000.00Now, I know this minor modification would not really further my understanding of GROUPING SETS, but I am wondering why the second result set shows non-NULL EmpID and non-NULL Yr showing NULL values for "Month" twice, as opposed to the first result set, which shows NULL values for "Month" once. Thank you.
- Edited by CatGuy90 Sunday, December 30, 2012 2:20 AM
All Replies
-
Sunday, December 30, 2012 6:38 AM
Hi
Using the 'ROLLUP' result set that shows aggregates for a hierarchy of values in the selected columns.
See as below link you will get your answer :
http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
- Edited by Ahsan Kabir Sunday, December 30, 2012 7:02 AM
- Edited by Ahsan Kabir Sunday, December 30, 2012 7:04 AM
-
Sunday, December 30, 2012 3:40 PMModerator
Where you see NULL in year or month, that is a ROLLUP sum.
SELECT SalesPersonId, Month(OrderDate) as Month, YEAR(OrderDate) AS Yr, SUM(Subtotal) AS SalesRevised FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY GROUPING SETS((SalesPersonId, ROLLUP( Month(OrderDate), YEAR(OrderDate)))) ORDER BY SalesPersonID, Yr, Month;
SalesPersonId Month Yr SalesRevised 274 NULL NULL 1092123.856 274 1 NULL 70815.3593 274 2 NULL 158508.5048 274 3 NULL 19721.994 274 4 NULL 117987.522 274 5 NULL 103445.8872 274 6 NULL 83789.6174 274 7 NULL 3575.7202 274 8 NULL 205587.8662 274 9 NULL 90682.7153 274 10 NULL 58156.9716 274 11 NULL 99857.5238 274 12 NULL 79994.1743 274 8 2005 20544.7015 274 9 2005 2039.994 274 11 2005 6341.551 274 2 2006 61206.4782 274 3 2006 18307.746 274 4 2006 33406.7043 274 6 2006 44670.6854 274 7 2006 3575.7202 274 8 2006 55616.5989 274 9 2006 523.788 274 10 2006 56210.9496 274 11 2006 2709.6518 274 12 2006 79994.1743 274 2 2007 97302.0266 274 4 2007 43254.2036 274 5 2007 5255.3088 274 6 2007 1466.01 274 8 2007 129426.5658 274 9 2007 88118.9333 274 10 2007 1946.022 274 11 2007 90806.321 274 1 2008 70815.3593 274 3 2008 1414.248 274 4 2008 41326.6141 274 5 2008 98190.5784 274 6 2008 37652.922 275 NULL NULL 9293903.005 275 1 NULL 515114.951 275 2 NULL 667605.3815 275 3 NULL 636774.893 275 4 NULL 655356.5431 275 5 NULL 791163.0841 275 6 NULL 824214.4068 275 7 NULL 731672.5501 275 8 NULL 999607.5421 275 9 NULL 1047047.146 275 10 NULL 649258.7996 275 11 NULL 1019925.121 275 12 NULL 756162.5864 275 7 2005 63762.9228 275 8 2005 108543.0328 275 9 2005 132449.647 275 10 2005 107814.6255 275 11 2005 173840.7687 275 12 2005 165330.1086 275 1 2006 124082.7264 275 2 2006 155831.1082 275 3 2006 128000.9617 275 4 2006 143767.8366 275 5 2006 172429.5757 275 6 2006 126619.075 275 7 2006 419617.3361 275 8 2006 441266.3502 275 9 2006 384621.5095 275 10 2006 319261.3372 275 11 2006 439082.5818 275 12 2006 222617.5258 275 1 2007 229113.0096 275 2 2007 193228.6873 275 3 2007 260648.3902 275 4 2007 314936.4504 275 5 2007 376270.9093 275 6 2007 327588.3495 275 7 2007 248292.2912 275 8 2007 449798.1591 275 9 2007 529975.9896 275 10 2007 222182.8369 275 11 2007 407001.7703 275 12 2007 368214.952 275 1 2008 161919.215 275 2 2008 318545.586 275 3 2008 248125.5411 275 4 2008 196652.2561 275 5 2008 242462.5991 275 6 2008 370006.9823 276 NULL NULL 10367007.43 276 1 NULL 197302.812 276 2 NULL 1409772.284 276 3 NULL 606213.1507 276 4 NULL 322844.2947 276 5 NULL 1537669.503 276 6 NULL 665262.1965 276 7 NULL 425009.5451 276 8 NULL 1578472.898 276 9 NULL 925822.6657 276 10 NULL 313495.7953 276 11 NULL 1534398.766 276 12 NULL 850743.5157 276 7 2005 5475.9485 276 8 2005 270964.9036 276 9 2005 182616.4848 276 10 2005 5957.9683 276 11 2005 389311.0194 276 12 2005 289493.3297 276 1 2006 5895.671 276 2 2006 380517.3823 276 3 2006 205046.644 276 4 2006 3762.2367 276 5 2006 401687.5647 276 6 2006 119389.3001 276 7 2006 111341.2911 276 8 2006 647900.5965 276 9 2006 410184.0714 276 10 2006 98966.6857 276 11 2006 578360.1944 276 12 2006 271944.0574 276 1 2007 73709.7309 276 2 2007 532098.9188 276 3 2007 164516.324 276 4 2007 88379.2611 276 5 2007 614957.4404 276 6 2007 263161.852 276 7 2007 308192.3055 276 8 2007 659607.3976 276 9 2007 333022.1095 276 10 2007 208571.1413 276 11 2007 566727.5525 276 12 2007 289306.1286 276 1 2008 117697.4101 276 2 2008 497155.983 276 3 2008 236650.1827 276 4 2008 230702.7969 276 5 2008 521024.4976 276 6 2008 282711.0444 277 NULL NULL 10065803.54 277 1 NULL 501863.1189 277 2 NULL 793136.7786 277 3 NULL 731405.7776 277 4 NULL 725665.5177 277 5 NULL 1057581.897 277 6 NULL 879164.5058 277 7 NULL 708226.2811 277 8 NULL 1018282.834 277 9 NULL 1014453.966 277 10 NULL 669521.6478 277 11 NULL 987924.4508 277 12 NULL 978576.7655 277 7 2005 46695.5564 277 8 2005 267192.6632 277 9 2005 141197.1064 277 10 2005 93361.01 277 11 2005 396569.1743 277 12 2005 302418.9271 277 1 2006 64192.8544 277 2 2006 319637.6017 277 3 2006 267190.1492 277 4 2006 110803.1498 277 5 2006 464167.3123 277 6 2006 264112.371 277 7 2006 399424.9953 277 8 2006 405976.5067 277 9 2006 457596.1539 277 10 2006 333178.6622 277 11 2006 365941.8222 277 12 2006 351146.8154 277 1 2007 272204.4129 277 2 2007 305926.6215 277 3 2007 186124.981 277 4 2007 400651.4944 277 5 2007 383608.9199 277 6 2007 277065.913 277 7 2007 262105.7294 277 8 2007 345113.6639 277 9 2007 415660.7059 277 10 2007 242981.9756 277 11 2007 225413.4543 277 12 2007 325011.023 277 1 2008 165465.8516 277 2 2008 167572.5554 277 3 2008 278090.6474 277 4 2008 214210.8735 277 5 2008 209805.6644 277 6 2008 337986.2218 278 NULL NULL 3609447.215 278 1 NULL 47844.7672 278 2 NULL 350501.9715 278 3 NULL 265255.0988 278 4 NULL 52090.2674 278 5 NULL 561802.7601 278 6 NULL 245796.1795 278 7 NULL 32180.0383 278 8 NULL 753390.2756 278 9 NULL 308217.2339 278 10 NULL 51912.0077 278 11 NULL 624439.8945 278 12 NULL 316016.7203 278 7 2005 9109.1683 278 8 2005 156524.7934 278 9 2005 41701.5422 278 10 2005 35469.3308 278 11 2005 196792.6726 278 12 2005 32772.4151 278 1 2006 27721.8978 278 2 2006 113238.5949 278 3 2006 85480.6323 278 4 2006 39176.0371 278 5 2006 172622.3542 278 6 2006 19650.0272 278 7 2006 6410.9419 278 8 2006 326526.0907 278 9 2006 89518.9572 278 10 2006 3025.7529 278 11 2006 224273.3573 278 12 2006 80727.3666 278 1 2007 2852.5385 278 2 2007 120066.9786 278 3 2007 68720.8323 278 4 2007 8091.5083 278 5 2007 214520.8152 278 6 2007 80733.1441 278 7 2007 16659.9281 278 8 2007 270339.3915 278 9 2007 176996.7345 278 10 2007 13416.924 278 11 2007 203373.8646 278 12 2007 202516.9386 278 1 2008 17270.3309 278 2 2008 117196.398 278 3 2008 111053.6342 278 4 2008 4822.722 278 5 2008 174659.5907 278 6 2008 145413.0082 279 NULL NULL 7172567.331 279 1 NULL 377106.1181 279 2 NULL 713404.9845 279 Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012- Unproposed As Answer by CatGuy90 Monday, December 31, 2012 7:35 AM
-
Monday, December 31, 2012 7:33 AMYes, in response to both of your answers, I believe I understand the basics of Rollup. However, I just want to know why, in my example, the result set shows "NULL" for "Month" two times for the same "EmpID", "Yr" and "SalesRevised". Thank you.
-
Monday, December 31, 2012 1:56 PM
Consider this nonsensical query:
SELECT schema_id, COUNT(*)
FROM sys.objects
GROUP BY GROUPING SETS (schema_id, schema_id)The output in my tempdb is:
schema_id
----------- -----------
1 4
4 54
1 4
4 54Looking in the topic for GROUP BY in Books Online, I found this passage:
Duplicate grouping sets in a GROUPING SETS list are not eliminated. Duplicate grouping sets can be generated by specifying a column expression more than one time or by listing a column expression also generated by a CUBE or ROLLUP in the GROUPING SETS list.
I will have to assume that this is compliant with the ANSI standard, since GROUPING SETS in SQL Server is drawn from ANSI.
ROLLUP(a, b) is a shortcut for:
(a, b), (a), ()
And thus, in your query you have Yr included twice.
As for why duplicates are not eliminated or yield an error, I am afraid I am not in position to answer that question.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked As Answer by HunchbackMVP, Moderator Monday, December 31, 2012 3:40 PM
- Unmarked As Answer by HunchbackMVP, Moderator Monday, December 31, 2012 3:40 PM
- Proposed As Answer by HunchbackMVP, Moderator Monday, December 31, 2012 3:40 PM
- Marked As Answer by CatGuy90 Monday, December 31, 2012 8:16 PM
-
Monday, December 31, 2012 4:24 PMModerator
This link will help you to understand better Erland's comment.
GROUPING SETS Equivalents
http://msdn.microsoft.com/en-us/library/bb510427(v=sql.105).aspxHere is the equivalent for the original statement:
-- simulate statement
SELECT
[object_id],
column_id,
column_id AS column_id_2,
system_type_id,
COUNT(*) AS cnt
FROM
sys.columns
GROUP BY
GROUPING SETS (
([object_id], ROLLUP(column_id, system_type_id))
);
-- equivalent
SELECT
[object_id],
column_id,
column_id AS column_id_2,
system_type_id,
COUNT(*) AS cnt
FROM
sys.columns
GROUP BY
GROUPING SETS (
([object_id], column_id, system_type_id),
([object_id], column_id),
([object_id])
);Here is the equivalent for the second statement:
-- simulate statement
SELECT
[object_id],
column_id,
column_id AS column_id_2,
system_type_id,
COUNT(*) AS cnt
FROM
sys.columns
GROUP BY
GROUPING SETS (
([object_id], column_id, ROLLUP(column_id, system_type_id))
);
-- equivalent
SELECT
[object_id],
column_id,
column_id AS column_id_2,
system_type_id,
COUNT(*) AS cnt
FROM
sys.columns
GROUP BY
GROUPING SETS (
([object_id], column_id, system_type_id),
([object_id], column_id, column_id),
([object_id], column_id)
);Notice that the last two grouping sets are similar. Duplicate columns in the grouping set definition don't create more or less levels.
I have to admit that grouping sets algebra is not trivial. Here is a good series of articles to understand this topic.
Itzik also touched this topic in his last book about T-SQL Querying.
Inside Microsoft® SQL Server® 2008: T-SQL Querying
http://shop.oreilly.com/product/9780735626034.do
AMB
Some guidelines for posting questions...
- Edited by HunchbackMVP, Moderator Monday, December 31, 2012 4:26 PM
- Edited by HunchbackMVP, Moderator Monday, December 31, 2012 4:49 PM
.png)
