In this article I\u2019ll demonstrate how to achieve the same results using each method.<\/p>\n
Prepare the data set<\/h2>\n
All queries in this article will run in the AdventureWorks2012 database. If you wish to follow along with this article, download it from here<\/a>.<\/p>\n Imagine you\u2019re working as a data analyst at the bike manufacturer Adventure Works, and you\u2019re interested in the company\u2019s income over the last few years. This means you need to group the company\u2019s income per year and run the following query:<\/p>\n Query 1. <\/strong>Income by year<\/p>\n Query 1 returns the following result set:<\/p>\n OrderYear<\/p>\n<\/td>\n Income<\/p>\n<\/td>\n<\/tr>\n<\/thead>\n 2005<\/p>\n<\/td>\n 11331809<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 30674773.2<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 42011037.2<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 25828762.1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Table 1.<\/strong> Company’s income per year.<\/p>\n According to Table 1, the company have been registering income between 2005 and 2008. Assuming that the currency is in US dollars, in 2005 their income was around eleven million dollars. In 2006 it was around thirty million dollars, and so on. This kind of information would be useful for supporting a business decision such as opening a company extension elsewhere.<\/p>\n However, if you still want more details about the company\u2019s income, you must perform a new grouping by adding a column or expression to the GROUP BY clause. Add the order month to the previous set of group by expressions. By doing this, the query will return the company\u2019s income per year and month. Review the GROUP BY clause in the following query.<\/p>\n Query 2.<\/strong> Company\u2019s income per year and month.<\/p>\n The following table contains the result set of Query 2:<\/p>\n OrderYear<\/p>\n<\/td>\n OrderMonth<\/p>\n<\/td>\n Income<\/p>\n<\/td>\n<\/tr>\n<\/thead>\n 2005<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n 962716.742<\/p>\n<\/td>\n<\/tr>\n 2005<\/p>\n<\/td>\n 8<\/p>\n<\/td>\n 2044600<\/p>\n<\/td>\n<\/tr>\n 2005<\/p>\n<\/td>\n 9<\/p>\n<\/td>\n 1639840.11<\/p>\n<\/td>\n<\/tr>\n 2005<\/p>\n<\/td>\n 10<\/p>\n<\/td>\n 1358050.47<\/p>\n<\/td>\n<\/tr>\n 2005<\/p>\n<\/td>\n 11<\/p>\n<\/td>\n 2868129.2<\/p>\n<\/td>\n<\/tr>\n 2005<\/p>\n<\/td>\n 12<\/p>\n<\/td>\n 2458472.43<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 1<\/p>\n<\/td>\n 1309863.25<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n 2451605.62<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 3<\/p>\n<\/td>\n 2099415.62<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 4<\/p>\n<\/td>\n 1546592.23<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 5<\/p>\n<\/td>\n 2942672.91<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 6<\/p>\n<\/td>\n 1678567.42<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n 2894054.68<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 8<\/p>\n<\/td>\n 4147192.18<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 9<\/p>\n<\/td>\n 3235826.19<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 10<\/p>\n<\/td>\n 2217544.45<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 11<\/p>\n<\/td>\n 3388911.41<\/p>\n<\/td>\n<\/tr>\n 2006<\/p>\n<\/td>\n 12<\/p>\n<\/td>\n 2762527.22<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 1<\/p>\n<\/td>\n 1756407.01<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n 2873936.93<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 3<\/p>\n<\/td>\n 2049529.87<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 4<\/p>\n<\/td>\n 2371677.7<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 5<\/p>\n<\/td>\n 3443525.25<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 6<\/p>\n<\/td>\n 2542671.93<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n 3554092.32<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 8<\/p>\n<\/td>\n 5068341.51<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 9<\/p>\n<\/td>\n 5059473.22<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 10<\/p>\n<\/td>\n 3364506.26<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 11<\/p>\n<\/td>\n 4683867.05<\/p>\n<\/td>\n<\/tr>\n 2007<\/p>\n<\/td>\n 12<\/p>\n<\/td>\n 5243008.13<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 1<\/p>\n<\/td>\n 3009197.42<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n 4167855.43<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 3<\/p>\n<\/td>\n 4221323.43<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 4<\/p>\n<\/td>\n 3820583.49<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 5<\/p>\n<\/td>\n 5194121.52<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 6<\/p>\n<\/td>\n 5364840.18<\/p>\n<\/td>\n<\/tr>\n 2008<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n 50840.63<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Table 2.<\/strong> Company’s income per year and month.<\/p>\n This result set is more detailed than the former. In July 2005, their income was around nine hundred sixty thousand dollars. In August 2005, it was around two million dollars, and so on. The more expressions or columns added to the GROUP BY clause, the more detailed the results will be.<\/p>\n If you observe the structure of the two queries, you will see they\u2019re grouped by a single set of grouping expressions. The former is grouped by order year, and the latter is grouped by order year and month.<\/p>\n Suppose the business manager at Adventure Works wants to visualize both results within a single result set. To accomplish this, you may merge the previous queries \u2013 Query 1 and Query 2 \u2013 by using the UNION ALL<\/strong> operator. First, modify Query 1 by adding a dummy column so it will have the same number of columns as Query 2. All queries merged by the UNION<\/strong> operator must have the same number of columns. This dummy column will return NULL in the OrderMonth column, identifying the OrderYear total rows of this query. The UNION ALL<\/strong> query looks like this:<\/p>\n Query 3.<\/strong> Company\u2019s income per year and per year and month.<\/p>\n Figure 1 shows the result set produced by Query 3. Review the comments in the figure which identify the grouping sets.<\/p>\n Figure 1.<\/strong> Company’s income per year and per year and month. Notice the comments added to the figure.<\/p>\n This information doesn\u2019t look new, because you already know that in 2005 the company\u2019s income was around eleven million dollars. In July 2005 the company\u2019s income was around nine hundred sixty thousand dollars, and so on. What\u2019s new to you is that each grouping result \u2013year grouping result and year and month grouping result\u2013 is merged.<\/p>\n Maybe you\u2019ve figured out how the NULL values appeared in the result set. Remember you used the NULL as a dummy column to identify the results from the order year grouping. Look carefully at Figure 2 which details the placeholders in the first grouped query.<\/p>\n Figure 2.<\/strong> Pointing out the placeholders.<\/p>\n When there\u2019s more than one group by expression list involved in the query, a NULL is used as a placeholder to identify one of the groupings in the results. Looking at Figure 2 again, a row that has NULL in the OrderMonth column means the row belongs to the order year grouping. When the row has a value in both the OrderYear and OrderMonth columns, it means the row belongs to the order year and month grouping. This situation happens when one of the grouped queries doesn\u2019t have the same number of columns grouped. In this example, the first grouping is by order year and the second grouping is by order year and month.<\/p>\n Although you obtained the desired result, Query 3 would be even larger if you added another grouping set, such as order day. As a data analyst, you decided to search the internet to find a way to achieve the same results but with less work. You find that by using the GROUPING SETS<\/strong> operator you should get the same result set, but with less coding! This really motivates you, and you write the following query using GROUPING SETS<\/strong>:<\/p>\n Query 4.<\/strong> Getting the same result set produced by the Query #3 but using the GROUPING SETS clause.<\/p>\n The result set produced by Query 4 is the same as that displayed in Figure 1. Figure 3 shows the results, but the new technique requires less code. The GROUPING SETS<\/strong> operator is used along with the GROUP BY<\/strong> clause, and allows you to make multi-grouped queries just by specifying the grouping sets separated by comma. However, you need to be careful when specifying the grouping sets. For example, if a grouping contains two columns, say column A and column B, both columns need to be contained within parenthesis: (column A, column B). If there\u2019s not a parenthesis between them, the GROUPING SETS<\/strong> clause will define them as separate groupings, and the query will not return the desired results.<\/p>\n Figure 3.<\/strong> Company’s income per year and per year and month using the GROUPING SETS clause.<\/p>\n By the way, if you want to perform the aggregation over the entire result set without grouping but still use the GROUPING SETS<\/strong> operator, just add an empty parenthesis for the grouping set. Look at Query 5 which calculates the company\u2019s income per year, month, and overall total:<\/p>\n Query 5.<\/strong> Company\u2019s income per year, per year and month, and overall.<\/p>\n Notice the placeholders for the third grouping shown in Figure 4. The query calculated the grand total of incomes by just specifying an empty parenthesis as the third grouping set; the third grouping set is the sum of SubTotal for the table itself.<\/p>\n Figure 4.<\/strong> Company’s income per year, per year and month and all over the time.<\/p>\n By the way, if you\u2019ve asked yourself \u201cWhat would happen if the NULL is part of the data and isn\u2019t used as placeholder?\u201d or \u201cHow can I tell when NULL is used as placeholder or is just the value?\u201d In this example, I ensured that the grouped columns aren\u2019t nullable, so the NULLs are used as placeholders. In case the grouped columns are nullable, you will need to use the GROUPING<\/strong> or GROUPING_ID<\/strong> function to identify if the NULL came from the GROUPING SETS<\/strong> operator \u2013 it can also come with other groupings operators like ROLLUP<\/strong> and CUBE<\/strong>\u2013 or is part of the data. Both functions \u2013 GROUPING<\/strong> and GROUPING_ID<\/strong>\u2013 will be treated in another article.<\/p>\n In this article, you learned how to achieve an aggregate query with more than one grouping expression list by using the GROUPING SETS<\/strong> operator. Unlike other operators such as ROLLUP<\/strong> and CUBE<\/strong>, you must specify each grouping set. These grouping operators are very important for summarizing data and producing grand totals and sub totals. If you want more information about these operators, please read this<\/a> article.<\/p>\n I suggest that you practice what you’ve learned in this article; this topic is very important for anyone working with SQL Server data. The data volume is increasing very quickly, and it’s vital to summarize it for better knowledge about the business.<\/p>\n","protected":false},"excerpt":{"rendered":" Writing aggregate queries is one of the most important tasks for anyone working with T-SQL. Determining the expressions required in the GROUP BY clause is not that difficult, but what do you do if you need to include different combinations of group by expressions in the same result set? Alfonso demonstrates how to use the GROUPING SETS operator to accomplish this task.…<\/p>\n","protected":false},"author":316724,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5134],"coauthors":[50462],"class_list":["post-75947","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75947","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316724"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=75947"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75947\/revisions"}],"predecessor-version":[{"id":76158,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/75947\/revisions\/76158"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=75947"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=75947"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=75947"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=75947"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Case Study: Data Analyst at Adventure Works<\/h2>\n
USE AdventureWorks2012;\r\nGO\r\n\r\nSELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tSUM(SubTotal) AS Income\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate)\r\nORDER BY OrderYear;\r\nGO\r\n\r\n<\/pre>\n
\n\n
\n \n\n \n \n \n \n \n \n \n \n \n \n \n \n \n SELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tMONTH(OrderDate) AS OrderMonth,\r\n\tSUM(SubTotal) AS Income\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate), MONTH(OrderDate)\r\nORDER BY OrderYear, OrderMonth;\r\nGO\r\n\r\n<\/pre>\n
\n\n
\n \n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n SELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tNULL AS OrderMonth, --Dummy Column\r\n\tSUM(SubTotal) AS Incomes\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate)\r\nUNION ALL\r\nSELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tMONTH(OrderDate) AS OrderMonth,\r\n\tSUM(SubTotal) AS Incomes\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate), MONTH(OrderDate)\r\nORDER BY OrderYear, OrderMonth;\r\nGO\r\n\r\n<\/pre>\n
![\"http:\/\/www.sqlservercentral.com\/Images\/33648.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/11\/http-www-sqlservercentral-com-images-33648-jpg.jpeg\")
<\/p>\n![\"http:\/\/www.sqlservercentral.com\/Images\/33649.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/11\/http-www-sqlservercentral-com-images-33649-jpg.jpeg\")
<\/p>\nSELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tMONTH(OrderDate) AS OrderMonth,\r\n\tSUM(SubTotal) AS Incomes\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY\r\n\tGROUPING SETS\r\n\t(\r\n\t\tYEAR(OrderDate), --1st grouping set\r\n\t\t(YEAR(OrderDate),MONTH(OrderDate)) --2nd grouping set\r\n\t);\r\nGO\r\n\r\n<\/pre>\n
![\"http:\/\/www.sqlservercentral.com\/Images\/33650.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/11\/http-www-sqlservercentral-com-images-33650-jpg.jpeg\")
<\/p>\nSELECT\r\n\tYEAR(OrderDate) AS OrderYear,\r\n\tMONTH(OrderDate) AS OrderMonth,\r\n\tSUM(SubTotal) AS Incomes\r\nFROM Sales.SalesOrderHeader\r\nGROUP BY\r\n\tGROUPING SETS\r\n\t(\r\n\t\tYEAR(OrderDate), --1st grouping set\r\n\t\t(YEAR(OrderDate),MONTH(OrderDate)), --2nd grouping set\r\n\t\t() --3rd grouping set (grand total)\r\n\t);\r\nGO\r\n\r\n<\/pre>\n
![\"http:\/\/www.sqlservercentral.com\/Images\/33651.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/11\/http-www-sqlservercentral-com-images-33651-jpg.jpeg\")
<\/p>\nConclusion<\/h2>\n