Learning SQL Series – Grouping Data

In this edition of learning SQL we will give you an overview of grouping data, using the GROUP BY clause and the HAVING clause.

In learning SQL, the mastering of how to group data will lead to greater business intelligence insights and to the full unlocking of the potential of your database.

Grouping of Data a definition

Grouping is the process of summarizing a set of rows into a single row, based on specified criteria. Aggregate functions are used to combine values in one or more columns of the original set of rows into a single value in the summary row.

For example, we can add up orders by customer to find the total value of orders placed by each customer.

Working with the GROUP BY Clause

The GROUP BY clause is used to achieve grouping. This clause is written after the WHERE and ORDER BY clauses in a SQL statement.

The syntax for the GROUP BY clause is:

SELECT <column1,column2, …,columnN>, <aggregate_function>(column)
FROM <table>
WHERE <condition>
GROUP BY <column1,column2, …,columnN>

Consider the following query that will display the total value of each order.

SELECT OrderID,SUM(Quantity * UnitPrice) AS 'Total Order Value'
FROM [Order Details]
GROUP BY OrderID

Working with the HAVING Clause

Consider a query that uses the GROUP BY clause to calculate and display the total value of orders placed by each customer. We may want to view only those customers whose order values exceed a certain amount. In such a case, we will use the HAVING clause.

The HAVING clause is used to filter rows returned by the GROUP BY clause.

When you use the WHERE clause along with the GROUP BY and HAVING clauses, first the condition in the WHERE clause is used to create a result set of rows that meet the WHERE condition. Next, theGROUP BY clause is used to create the summary rows. Finally, the HAVING clause is applied to the summary rows to create the final result set.

If a GROUP BY is not used then the HAVING clause behaves exactly the same way as the WHEREclause.

The syntax for the HAVING clause is:

SELECT <column1, column2, …, columnN>, <aggregate_function> (column)
FROM <table_name>
[WHERE <condition>]
GROUP BY <column1, column2, …, columnN>
HAVING <condition>

Consider the query that retrieves orders having total value of each order more than 500.

SELECT OrderID, ProductID, SUM(Quantity * UnitPrice) AS 'Total Order Value'
FROM [Order Details]
WHERE ProductID BETWEEN 20 AND 35
GROUP BY OrderID, ProductID
HAVING SUM(Quantity * UnitPrice) > 500

Learning SQL Series - Grouping Data Summary

We hope this Learning SQL article has given you a better understanding of grouping data, using theGROUP BY clause and the HAVING clause.