{"id":94510,"date":"2022-05-27T20:43:56","date_gmt":"2022-05-27T20:43:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94510"},"modified":"2022-05-27T20:43:56","modified_gmt":"2022-05-27T20:43:56","slug":"summarizing-data-using-group-by-and-having-clauses","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/summarizing-data-using-group-by-and-having-clauses\/","title":{"rendered":"Summarizing data using GROUP BY and HAVING clauses"},"content":{"rendered":"

SQL Server provides the GROUP<\/code> BY<\/code> clause as a means to take a set of detailed values and summarize them into aggregated values. The detailed records can be aggregated based on one column, as well as expressions, which are specified in the GROUP<\/code> BY<\/code> clause. This article explores how to return summarized data using the GROUP<\/code> BY<\/code> and HAVING<\/code> clauses.<\/p>\n

Test data<\/h2>\n

In order to demonstrate how to use the GROUP<\/code> BY<\/code> and HAVING<\/code> clauses, some test data will be needed. All of the examples in this article will use a table called Invoice<\/code> that is created in tempdb<\/code>,<\/em> using the code in Listing 1.<\/p>\n

Listing 1: Code to create test data<\/strong><\/p>\n

USE tempdb;\r\nGO\r\nCREATE TABLE Invoice (\r\nInvoiceID INT,\r\nInvDT DATE,\r\nInvAmt DECIMAL(10,2),\r\nPaymentDT Date, \r\nPaymentAmt DECIMAL(10,2),\r\nProductName VARCHAR(20)\r\n)\r\nGO\r\nINSERT INTO Invoice VALUES\r\n(1,'2021-02-14',121.11,'2021-03-01',121.11,'Thingamajig'),\r\n(2,'2021-02-14',159.16,'2021-03-13',159.16,'Watchamacallit'),\r\n(3,'2021-02-14',75.59,'2021-03-03',75.16,'Dohicky'),\r\n(4,'2021-02-14',121.11,'2021-03-01',121.11,'Thingamajig'),\r\n(5,'2021-02-14',75.59,'2021-02-21',75.16,'Dohicky'),\r\n(6,'2021-03-03',159.16,Null,null,'Watchamacallit'),\r\n(7,'2021-03-11',75.59,'2021-03-30',75.16,'Dohicky'),\r\n(8,'2021-02-14',121.11,'2021-03-01',121.11,'Thingamajig'),\r\n(9,'2021-03-11',159.16,null,null,'Watchamacallit');\r\nGO<\/pre>\n
The code in Listing 1 created nine different invoice records in the Invoice<\/code> table. Each row contains a single invoice record for one of three different ProductName\u2019s<\/code>: <\/em>Thingamajig<\/code>, Watchamacallit<\/code>, and Dohicky<\/code>. Each row has an InvDT<\/code>, <\/em>and an InvAmt<\/code>, where the InvDT<\/code> identifies the date the product was sold, and the InvAmt<\/code> identifies the amount the product sold for. Each product sells for a different price, but each sale of a specific product sells for the same amount. The PaymentDT<\/code> and PaymentAmt<\/code> identify the date and amount each payment was made. A couple of the invoices have not been paid, hence why their PaymentDT<\/code> and PaymentAmt<\/code> are null.<\/p>\n
A few different examples will be provided that use the test data in the Invoice<\/code> table to demonstrate how the GROUP<\/code> BY<\/code> and HAVING<\/code> clauses work. If you would like to run the example code provided in this article, you can use the code in Listing 1 to create the Invoice <\/em>table on your instance of SQL Server.<\/p>\n
Summarizing based on a single column<\/h2>\n
This first example shows how to write a GROUP<\/code> BY<\/code> clause that aggregates the rows in the Invoice<\/code> table based on a single column grouping specification. This example calculates the total and average invoice amount for each product identified in the Invoice <\/em>table by using the code in Listing 2.<\/p>\n
Listing 2: Grouping based on a single column<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, \r\nSUM(InvAmt) TotalInvAmt, AVG(InvAmt) AvgInvAmt \r\nFROM Invoice\r\nGROUP BY ProductName;<\/pre>\n
Report 1 shows the results when Listing 2 is executed.<\/p>\n
Report 1: Results when Listing 2 is run<\/strong><\/p>\n
\"Image<\/p>\n
In Listing 2, the GROUP<\/code> BY<\/code> clause identifies only a single column named ProductName<\/code>.<\/em> This told SQL Server to group the rows by ProductName<\/em> when calculating the TotalInvAmt<\/code> and the AvgInvAmt<\/code>. <\/em>The TotalInvAmt<\/code> for each of the different products was calculated using the SUM<\/code> function, whereas the AvgInvAmt<\/code> was calculated using the AVG<\/code> function. Since there were only three different products sold in the Invoice <\/em>table, only three summarized amounts were returned, one for each ProductName<\/code>.<\/p>\n
When a SELECT<\/code> statement contains a GROUP<\/code> BY<\/code> clause, the columns in the select list can only be those columns identified in the GROUP<\/code> BY<\/code> clause, or column values calculated using an aggregate function. If there is no GROUP<\/code> BY<\/code> clause, then only aggregate functions can be added to the SELECT<\/code> list and ORDER<\/code> BY<\/code> clause. In this case, the query will return one row and provide summary calculations over the entire result set. If a non-group by column is identified in the select list or ORDER<\/code> BY<\/code> clause, and it is not contained in an aggregate function, an error similar to the error in Report 2 will be produced.<\/p>\n
Report 2: Error received when a column listed that is not contained in an aggregate function<\/strong><\/p>\n
Msg 8120, Level 16, State 1, Line 31\r\n\r\nColumn 'Invoice.InvDT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.<\/pre>\n
The code Listing 3 was run to generate the error in Report 2.<\/p>\n
Listing 3: Code containing selection list column not contained in aggregate function<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, InvDT, \r\nSUM(InvAmt) TotalInvAmt, AVG(InvAmt) AvgInvAmt \r\nFROM Invoice\r\nGROUP BY ProductName;<\/pre>\n
Returning a subset of summarized values<\/h2>\n
If only a subset of the summarized rows needs to be returned, then a WHERE<\/code> constraint can be used to identify the subset. The code in Listing 4 uses the WHERE<\/code> clause to only return the summarized values for the ProductName<\/code> Dohicky<\/em>.<\/p>\n
Listing 4: Constraining result set using WHERE clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, \r\nSUM(InvAmt) TotalInvAmt, AVG(InvAmt) AvgInvAmt \r\nFROM Invoice\r\nWHERE ProductName = 'Dohicky'\r\nGROUP BY ProductName;<\/pre>\n
When adding the WHERE<\/code> constraint to a SQL statement that contains a GROUP<\/code> BY clause, the WHERE<\/code> clause needs to be placed prior to the GROUP<\/code> BY<\/code> clause. The results shown in Report 3 are produced when the code in Listing 4 is executed.<\/p>\n
Report 3: Output produced when Listing 4 is executed <\/strong><\/p>\n
\"An<\/p>\n
Grouping based on multiple columns<\/h2>\n
There are times when data summarization needs to produce summarized values with more granularity than a single column. When this is needed, multiple columns can be specified in the GROUP<\/code> BY<\/code> criteria, as shown in Listing 5.<\/p>\n
Listing 5: Summarizing based on ProductName <\/em>and InvDT<\/em><\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, InvDT, \r\nSUM(InvAmt) TotalInvAmt, AVG(InvAmt) AvgInvAmt \r\nFROM Invoice\r\nGROUP BY ProductName, InvDT;<\/pre>\n
In Listing 5, two columns, ProductName<\/code> and InvDT<\/code>, <\/em>were identified in the GROUP<\/code> BY<\/code> clause. This tells SQL Server to summarize the test data based on each unique combination of these two column values. Report 4 contains the output created when Listing 5 is executed.<\/p>\n
Report 4: Output when Listing 5 is run<\/strong><\/p>\n
\"An<\/p>\n
Sorting summarized data<\/h2>\n
You may have noticed that in the prior example, the output in Report 4 was not sorted by ProductName<\/code> and InvDT<\/code>, <\/em>even though the data was grouped and summarized by ProductName<\/code> and InvDT<\/code>. <\/em> The ordering of the output is based on the unique combination of the grouped columns, and not the individual values of the columns in the GROUP<\/code> BY<\/code> clause. The only way to guarantee the output\u2019s order is to have an ORDER<\/code> BY<\/code> clause. The code in Listing 6 groups the data the same as in Listing 5, but the output is produced in a different order because an ORDER<\/code> BY<\/code> clause has been added in Listing 6.<\/p>\n
Listing 6: Sorting summarized data<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, InvDT, \r\nSUM(InvAmt) TotalInvAmt, AVG(InvAmt) AvgInvAmt \r\nFROM Invoice\r\nGROUP BY ProductName, InvDT\r\nORDER BY ProductName, InvDT;<\/pre>\n
When using the ORDER<\/code> BY<\/code> clause, it must be specified after the GROUP<\/code> BY<\/code> clause. Report 5 shows the output produced when the code in Listing 6 is executed.<\/p>\n
Report 5: Output with Listing 6 is run<\/strong><\/p>\n
\"Same<\/p>\n
By using the ORDER<\/code> BY<\/code> clause, the output in Report 5 is sorted by ProductName<\/code> and InvDT<\/code>.<\/p>\n
Using an expression in the GROUP BY clause<\/h2>\n
Expressions can also be used in the GROUP<\/code> BY<\/code> clause to identify the values to summarize on. To show how an expression can be used in the GROUP<\/code> BY<\/code> clause, look at the code in Listing 7.<\/p>\n
Listing 7: Using Expression in GROUP BY clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT CAST(YEAR(InvDT) as CHAR(4)) + '-' + RIGHT(MONTH(InvDT) + 100,2) YearMonth,\r\nSUM(InvAmt) TotalInvAmt , AVG(InvAmt) AvgInvAmt\r\nFROM Invoice\r\nGROUP BY  CAST(YEAR(InvDT) as CHAR(4)) + '-' + RIGHT(MONTH(InvDT) + 100,2);<\/pre>\n
The code in Listing 7 summarizes the rows in the Invoice<\/code> table based on the year and month of the invoice. The year and month values are calculated from the InvDT<\/code> using the following expression:<\/p>\n
CAST(YEAR(InvDT) as CHAR(4)) + ‘-‘ + RIGHT(MONTH(InvDT) + 100,2);<\/p>\n
This expression was specified on the GROUP<\/code> BY<\/code> clause to identify the values for summarizing. Remember that any column specified in the selection list either needs to be used with an aggregate function or by one of the GROUP<\/code> BY<\/code> values. Therefore, the expression identified can be included in the selection list, so the year and month values could be displayed in the result set, as shown in Report 6.<\/p>\n
Report 6: Output when Listing 7 is run<\/strong><\/p>\n
\"An<\/p>\n
Grouping NULL values<\/h2>\n
When comparing two different null values by design are not equal, but when grouping on a column that contain null values, the nulls are grouped together as if they are equal. To demonstrate, run the code in Listing 8.<\/p>\n
Listing 8: GROUP ON column with null values<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT PaymentDT, MIN(PaymentAmt) MinPaymentAmt\r\nFROM Invoice \r\nGROUP BY PaymentDT;<\/pre>\n
Report 7 shows results when Listing 8 is run.<\/p>\n
Report 7: Output when Listing 8 is executed<\/strong><\/p>\n
\"An<\/p>\n
There is a row in Report 7 that has NULL<\/code> for PaymentDT<\/code> column. This row was created when grouping together the two rows that don\u2019t have a PaymentDT<\/code> value. The value of NULL<\/code> under the MinPaymentAmt<\/code> column occurred because null values are ignored for all aggregate functions, except the COUNT(*)<\/code> function.<\/p>\n
Because nulls are grouped together, and most aggregate functions ignore null values, summarizing results using a GROUP<\/code> BY<\/code> clause might create some summarized values that some might consider inaccurate. To see how aggregate functions ignore nulls, review the rows with null values in the sample data by running the code in Listing 9.<\/p>\n
Listing 9: Displaying sample data<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, PaymentAmt\r\nFROM Invoice \r\nWHERE ProductName = 'Watchamacallit';<\/pre>\n
Report 8 shows the output created when Listing 9 is executed.<\/p>\n
Report 8: Output when Listing 9 is run<\/strong><\/p>\n
\"An<\/p>\n
In Report 8, the PaymentAmt<\/code> is null for two of the rows in the result set. To demonstrate how aggregate functions ignore nulls, consider the code in Listing 10.<\/p>\n
Listing 10: Calculating average <\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT AVG(PaymentAmt) AvgPaymentAmt\r\nFROM Invoice \r\nWHERE ProductName = 'Watchamacallit';<\/pre>\n
When Listing 10 is executed, it returns the value 159.160000<\/em> for the AvgPaymentAmt<\/code>. <\/em>Is that the correct average amount? If you expected the average to be calculated based on three rows with a ProductName<\/code> of Watchamacallit, then it is not correct. Because SQL Server ignores nulls when using the AVG<\/code> function, it doesn\u2019t consider the two rows with nulls when determining the average amount. Since nulls are ignored when summarizing the data, the average value of 159.16<\/em> is correct.<\/p>\n
Using the GROUP<\/code> BY<\/code> clause in conjunction with the AVG<\/code> function to calculate the average for each ProductName<\/code> also ignores the null values, as can be seen by reviewing the output created when the code in Listing 11 is executed.<\/p>\n
Listing 11: Calculating the average for each ProductName<\/em><\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, AVG(PaymentAmt) AvgPaymentAmt\r\nFROM Invoice \r\nGROUP BY ProductName;<\/pre>\n
Report 9 shows out output when Listing 11 is run.<\/p>\n
Report 9: Output when Listing 10 is executed<\/strong><\/p>\n
\"An<\/p>\n
If the rows with null values need to be considered when calculating the average, then the nulls need to be converted to 0.00 prior to the AVG<\/code> function being processed, as is shown in Listing 12.<\/p>\n
Listing 12: Considering rows with a null value when calculating average<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, AVG(ISNULL(PaymentAmt, 0.00)) AvgPaymentAmt\r\nFROM Invoice \r\nGROUP BY ProductName;<\/pre>\n
Report 10 shows the output when Listing 12 is executed.<\/p>\n
Report 10: Output when Listing 12 is run<\/strong><\/p>\n
\"An<\/p>\n
The code in Listing 12 used the ISNULL<\/code> function to convert null values for the PaymentAmt<\/code> column to 0.00 prior to the AVG<\/code> function being applied to calculate the average amount. By doing this, the AVG<\/code> function considered all rows with ProductName<\/code> Watchamacallit<\/em> rows when calculating the AvgPaymentAmt<\/code>.<\/em><\/p>\n
HAVING clause<\/h2>\n
The HAVING<\/code> clause is used to filter out summarized rows created by the GROUP<\/code> BY<\/code> clause. The WHERE<\/code> clause is similar to the HAVING<\/code> clause. The difference is that the WHERE<\/code> clause filters out rows prior to them being summarized, whereas the HAVING<\/code> clause filters the rows after they have been summarized into the groups identified in the GROUP<\/code> BY<\/code> clause. To demonstrate how the HAVING<\/code> clause filters out summarized rows, consider the code in Listing 13.<\/p>\n
Listing 13: Using the HAVING clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT ProductName, AVG(ISNULL(PaymentAmt, 0.00)) AvgPaymentAmt\r\nFROM Invoice \r\nGROUP BY ProductName\r\nHAVING AVG(ISNULL(PaymentAmt, 0.00)) > 60;<\/pre>\n
Report 11 shows output when Listing 13 is run.<\/p>\n
Report 11: Output when Listing 13 is executed<\/strong><\/p>\n
\"An<\/p>\n
The HAVING<\/code> clause in Listing 13 only returned summarized rows that had an AvgPaymentAmt<\/code> greater than 60. By comparing Report 11 to Report 10, you can see the Whatchamacallit<\/em> row was removed by the HAVING<\/code> clause.<\/p>\n
Summarizing data using GROUP BY and HAVING clauses<\/h2>\n
This article covered how to take detail data and roll it up to produce summarized data using the GROUP<\/code> BY<\/code> and HAVING<\/code> clauses. Data can be summarized based on a single column or multiple columns, and even expressions can be used. Null values cannot be compared because they are unknown values. But when using the GROUP<\/code> BY<\/code> clause, null values are treated as if they are the same value for aggregation purposes. Using the GROUP<\/code> BY<\/code> clause does cause SQL Server to sort the detail rows to perform aggregation, but only by including the ORDER<\/code> BY<\/code> clause can the output order be guaranteed. Next time you need to summarize, filter, and display aggregated data, you will know how to use the GROUP<\/code> BY<\/code> and HAVING<\/code> clauses in conjunction with the WHERE<\/code> and ORDER<\/code> BY<\/code> clauses to produce the rolled-up data you require.<\/p>\n
If you liked this article, you might also like\u00a0 The ins and outs of joining tables in SQL Server\u00a0<\/a>.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
You can summarize data to get counts, averages, sums, and more using GROUP BY in T-SQL queries. Greg Larsen shows you how.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525,143531],"tags":[124831,4760,5134],"coauthors":[11330],"class_list":["post-94510","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-t-sql-programming-sql-server","tag-aggregation","tag-group-by","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94510","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\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94510"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94510\/revisions"}],"predecessor-version":[{"id":94547,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94510\/revisions\/94547"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94510"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}