Summarizing Data
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:
- What is the Difference between ORDER and GROUP BY?
- What is the difference between WHERE and HAVING clauses?
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
Latest Posts
-
SQL GROUP BY Guide
Use the SQL GROUP BY Clause is to consolidate like values into a single row. The group is a set of columns. Group by SQL 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…
-
SQL SUM Function
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…
-
What is the difference between WHERE and HAVING clauses?
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?…
-
SQL COUNT Function
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…
-
SQL APPROX_COUNT_DISTINCT Function
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…
-
SQL MIN Function
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 …
-
SQL MAX Function
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…
-
SQL AVG Function
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,…
-
SQL Pivot Table – Learn to Create in Six Steps
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…
-
Use SQL to Calculate a Running Total
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…
-
What is a Dynamic Pivot Table?
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…
-
What is the Difference between ORDER and GROUP BY?
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…