Intro
The GROUP BY statement groups rows that have the same values into summary rows
The GROUP BY statement is often used with aggregate functions to group the result-set by one or more columns
Sample Data
This is the table we will use as an example:
SELECT * FROM ar_invoices
| invoice# | customer# | salesperson# | invoice_date | invoice_amount | amount_paid | current_month_interest_charges | total_interest_charges |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2019-06-17 10:43:53.727 | 100.00 | 1.00 | 0.00 | 0.00 |
| 2 | 2 | 2 | 2019-05-28 10:43:53.727 | 50.00 | 0.00 | 5.00 | 10.00 |
| 3 | 3 | 2 | 2019-06-17 10:43:53.727 | 70.00 | 0.00 | 10.00 | 20.00 |
| 4 | 2 | 1 | 2019-07-07 10:43:53.727 | 80.00 | 0.00 | 0.00 | 0.00 |
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s)
SELECT salesperson#,
SUM(invoice_amount) AS [Total Sales]
FROM ar_invoices
GROUP BY salesperson#
| salesperson# | Total Sales |
|---|---|
| 1 | 180.00 |
| 2 | 120.00 |
Notice that if you add more columns to the SELECT statement,
you must also add them to the GROUP BY statement or recieve an error such as this:
SELECT salesperson#,
total_interest_charges,
SUM(invoice_amount) AS [Total Sales]
FROM ar_invoices
GROUP BY total_interest_charges
| Msg 8120 | Level 16 | State 1 | Line 22 |
|---|---|---|---|
| Column 'ar_invoices.salesperson#' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. |
This is the correct way:
SELECT salesperson#,
total_interest_charges,
SUM(invoice_amount) AS [Total Sales]
FROM ar_invoices
GROUP BY total_interest_charges,
salesperson#
| salesperson# | total_interest_charges | Total Sales |
|---|---|---|
| 1 | 0.00 | 180.00 |
| 2 | 10.00 | 50.00 |
| 2 | 20.00 | 70.00 |
Combine aggregates
Create summaries by combining aggregate functions
SELECT customer#,
COUNT(salesperson#) AS [Number of Employees that Customer Used],
SUM(invoice_amount) AS [Total Sales]
FROM ar_invoices
GROUP BY customer#
| customer# | Number of Employees that Customer Used | Total Sales |
|---|---|---|
| 1 | 1 | 100.00 |
| 2 | 2 | 130.00 |
| 3 | 1 | 70.00 |
HAVING
Use the HAVING clause to filter the results of an aggregate function
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)
SELECT salesperson#,
SUM(invoice_amount) AS [Total Sales]
FROM ar_invoices
GROUP BY salesperson#
HAVING SUM(invoice_amount) > 130
| salesperson# | Total Sales |
|---|---|
| 1 | 180.00 |