When you group and summarize SQL results, you use the GROUP BY Clause to output a row across specified column values. It is typically used in conjunction with aggregate functions such as SUM or Count to summarize values. In SQL, groups are unique combinations of fields. Rather than returning every row in a table, when values are grouped, only the unique combinations are returned.
If you want to start to learn about grouping data, I would recommend first reading this article: How to Use GROUP BY to Summarize Query Results
Once you understand how to group and summarize data, I would recommend reading the following:
These article will help you understand the subtle, yet important differences between these commands.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server
Use SQL HAVING to filter summary results from a GROUP BY. It specifies the search condition for the group or aggregate. SQL HAVING is only used with SELECT. It is mostly used when a GROUP BY is present, if one isn’t there is an implicit single aggregated group. When constructing a SELECT statement using HAVING…
Use the SQL GROUP BY Clause is to consolidate like values into a single row. The group is a set of columns. The group by returns a single row from one or more within the query having the same column values. Its main purpose is this work alongside functions, such as SUM or COUNT, and provide a…
The SQL SUM function returns the total value within a table or group. In its simplest use, SUM() totals a column for all results and returns a single value. In this example the query returns the total orders for all SalesOrderDetail records SELECT SUM(LineTotal) AS OrderTotal FROM Sales.SalesOrderDetail You can also filter your results prior to totaling. Here the SUM() is restricted to two…
In this article learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes! All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server. How do Where and Having Differ?…
The SQL COUNT function is an aggregate function used to count rows. Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group. Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL. Use DISTINCT with…
Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for very large tables where performance matters over precision. The latest 2019 version of SQL Server introduced many functions to the system and enriched the database engine in order to make it work faster…
The SQL MIN function returns the smallest value within a table or group. Throughout this section we’ll use the HumanResource.Employee table for our examples: Using the data above, MIN calculates the smallest SickLeaveHours amount for the entire table: When used with GROUP BY, MIN returns the largest value within a group. Here is a similar query showing the minimum SickLeaveHours by JobTitle: Additional SQL MIN Resources …
The SQL MAX function returns the largest value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using the data above, MAX calculates the largest SickLeaveHours amount for the entire table: When used with GROUP BY, MAX returns the largest value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL MAX Resources To learn…
The SQL AVG function returns the average value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using sample data above, AVG calculates the average SickLeaveHours amount for the entire table: When used with GROUP BY, AVG returns the average value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle: Additional SQL AVG Resources To learn more,…
In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart. I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these! Once you get the steps down, you’ll see they are not…
The running total in SQL can be calculated in several ways. This article will cover two methods: the Joins and the Window functions. We will first look at how to calculate the running total using the INNER JOIN. By doing so, you’ll not only learn more about join conditions, but see how to take the result…
In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server. A dynamic pivot table is a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. By reading this article you’ll learn to create a dynamic pivot…
Both the GROUP and ORDER BY clauses are used in organizing data. Find out which of the two is used to sort the data and which is used for counting and summing up. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started…