Summary of SQL Server data five strokes easy _mssql

In this article we will discuss how to use the GROUPBY clause to summarize the data.

Grouping with separate columns

The GROUP BY clause summarizes the data by setting the grouping criteria, and in the first example, I do data grouping on a column in the table Sales.SalesOrderDetail in the database AdventureWork2012. This example and other examples use database AdventureWorks2012, and if you want to use it to run my code, you can click on the download.

The following is the source code for the first example, using the GROUP BY clause on the Carriertrackingnumber column for data grouping operations

Use AdventureWorks2012; 
 
Go 
 
SELECT carriertrackingnumber 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail 
 
GROUP by Carriertrackingnumber; 

After I run this code, I get 3,807 records, and here's the first five values in this huge result set:

Carriertrackingnumber linetotal 
 
---------------------------------------------------------- 
 
6e46-440a-b5 2133.170700 
 
b501-448e-96 4085.409800 
 
8551-4cdf-a1 72616.524200 
 
b65c-4867-86 714.704300 
 
16185.429200 

In the example above, I use the group By clause Select which columns are the criteria for aggregating DventureWorks2012.Sales.SalesOrderDetai data table data, in the example, I use Carriertrackingnumber to summarize the data, and when you group data, only the group The Liecai that appears in the By clause is valid in the selection list. In my example, I used the aggregate function sum to compute linetotal, and I set an alias summarizedlinetotal for ease of use.

If I want to get carriertrackingnumber to meet a clustered set under certain conditions, then I can limit the query in the WHERE clause, as I do below:

Use AdventureWorks2012; 
 
Go 
 
SELECT carriertrackingnumber 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail 
 
WHERE carriertrackingnumber = ' 48f2-4141-9a ' 
 
GROUP by Carriertrackingnumber; 

Here I added a restriction to the WHERE clause based on the original query, and I set my query to return only the result of carriertrackingnumber equal to a particular value. After running this code, I get the number of rows in the record that Carriertrackingnumber equals 48f2-4141-9a. The filter behavior of the WHERE clause is in effect before the data is aggregated.

Grouping by multiple columns

Sometimes you might want to use multiple columns for data grouping, and here's the example code I use to group multiple columns

SELECT D.productid 
 
, H.orderdate 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail d 
 
JOIN AdventureWorks2012.Sales.SalesOrderHeader H on 
 
D. SalesOrderId = H.salesorderid 
 
GROUP by ProductID, OrderDate; 

The query returns 26878 rows of data, which is part of the results returned by the query above:

ProductID OrderDate linetotal 
 
----------------------------------------------------------
   714 2008-05-21 00:00:00.000 99.980000 
 
859 2007-11-03 00:00:00.000 48.980000 
 
923 2007-11-23 00:00:00.000 14.970000 
 
712 2007-12-22 00:00:00.000 62.930000 
 
795 2007-10-14 00:00:00.000 2443.350000 
 
950 2007-07-01 00:00:00.000 2462.304000 
 
795 2007-11-06 00:00:00.000 2443.350000 
 
877 2007-11-19 00:00:00.000 15.900000 
 
713 2007-10-01 00:00:00.000 99.980000 
 
860 2008-05-31 00:00:00.000 48.980000 
 
961 2008-05-01 00:00:00.000 36242.120880 

In the example program above, the ProductID column and the OrderDate column are used in the GROUP BY clause, and SQL Server returns the LineTotal value based on the uniqueness of both ProductID and OrderDate combinations. and set the alias Summarizedlinetotal for it. If you look at the output of the program, you will find that SQL Server groups the data, the results returned are not in a specific order, and if you need to return the results in a certain order, you need to use the ordering by clause, as I have shown in the following code.

SELECT D.productid 
 
, H.orderdate 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail d 
 
JOIN AdventureWorks2012.Sales.SalesOrderHeader H on 
 
D. SalesOrderId = H.salesorderid 
 
GROUP by ProductID, OrderDate order by 
 
Summarizedlinetotal DESC; 

In the code above, I sort the result set in descending order of summorizedlinetotal, and the value of this column is grouped by the GROUP BY clause to linetotal using the aggregate function sum. I sort the results in descending order by summorizedlinetotal values. If you run this program, you can draw the highest number of LineTotal ProductID and OrderDate.

grouping data without any values

Sometimes you need to group data that contains null values in some records. When you perform this type of operation in SQL Server, it automatically assumes that all null values are equal. Let me take a look at the following sample program

CREATE TABLE nullgroupby (OrderDate date, Amount Int); 
 
INSERT into nullgroupby values (null,100), 
 
(' 10-30-2014 ', MB), ( 
 
' 10-31-2014 ', MB), 
 
(null,100); 
 
SELECT OrderDate, SUM (Amount) as TotalAmount from 
 
nullgroupby 
 
GROUP by OrderDate; 
 
DROP TABLE nullgroupby; 
 
When I run this code I get the following output: 
 
OrderDate totalamount 
 
--------------------- 
 
NULL 
 
2014-10-30 
 
2014-10-31 100 

In the above program, I first created and populated a nullgroupby table. In this table, I placed four different rows, the first and last row of the OrderDate column value is null, the other two columns of the OrderDate value is different. As you can see from the output above, SQL Server aggregates two orderdate-null rows into one row while grouping.

To use an expression in a GROUP BY clause

Sometimes you need to use an expression in the GROUP BY clause, not a specific column. SQL Server allows you to specify an expression in the GROUP BY clause, as shown in the following code:

SELECT CONVERT (CHAR (7), h.orderdate,120) as [yyyy-mm] 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail d 
 
JOIN AdventureWorks2012.Sales.SalesOrderHeader H on 
 
D. SalesOrderId = H.salesorderid 
 
GROUP by CONVERT (CHAR (7), h.orderdate,120) Order by 
 
Summarizedlinetotal DESC; 

The above code is grouped using the year data in the OrderDate column, and by using the expression convert (CHAR (7), h.orderdate,120) I tell the SQL Server to intercept the first seven characters of the OrderDate ODBC standard date format. Which is the OrderDate yyyy-mm part. Based on this expression, I can find the total summarizelinetotal value for a given number of years, use an expression in the GROUP BY clause, and sort the linetotal value. I can find out which year is the largest or smallest of summarizelinetotal one months.

Filtering data using the HAVING clause

Having is another important clause that can be used in conjunction with the GROUP BY clause, and you can filter out rows of data that do not conform to an expression of the HAVING clause, and when I use the WHERE clause on it, the filter behavior occurs before aggregation. The HAVING clause allows you to filter aggregation rows based on certain criteria. To understand the HAVING clause more clearly, refer to the following code:

SELECT D.productid 
 
, H.orderdate 
 
, SUM (linetotal) as Summarizedlinetotal from 
 
AdventureWorks2012.Sales.SalesOrderDetail d 
 
JOIN AdventureWorks2012.Sales.SalesOrderHeader H on 
 
D. SalesOrderId = H.salesorderid 
 
GROUP by ProductID, OrderDate have 
 
SUM (linetotal) > 200000 ORDER by 
 
Summarizedlinetotal DESC; 

In the above code, the HAVING clause restricts the condition to sum (linetotal) > 200000. This HAVING clause guarantees that the aggregate value (summarizedlinetotal) of the linetotal in the final result is greater than 200000. By using the HAVING clause, my query returns only one row of data, and its summarizedlinetotal is greater than the 200000.having child The sentence allows SQL Server to return only the rows of data that aggregate results meet the conditions restricted by the HAVING clause.

Summarize

Many applications require that data be summarized before presentation, and the GROUP by clause is the mechanism by which SQL Server summarizes data. The GROUPBY clause allows you to use the HAVING clause to filter the summarized data specifically. Hopefully, the next time you need to roll up some data, you'll be able to achieve it more easily.