Summary Queries

Summary queries are queries that contain one or more aggregate functions.

Aggregate Functions

Aggregate functions operate on a set of rows to calculate and return a single value. For example, determining how many rows are returned; adding up values; or finding the highest, lowest, or average values in a column or group of the result set.

Common SQL Aggregate Functions

The AVG() Function Syntax:

AVG([ALL| DISTINCT] expression)

 SELECT AVG(salary) AS ‘Average Salary’
 FROM salaries
			

THE SUM() Function Syntax:

SUM([ALL| DISTINCT] expression)

 SELECT SUM(salary) AS ‘Total of all Salaries’
 FROM salaries
			

The MAX() Function Syntax:

MAX([ALL| DISTINCT] expression)

THE MIN() Function Syntax:

MIN([ALL| DISTINCT] expression)

 SELECT MAX(salary) AS ‘Highest Salary’, MIN(salary) As ‘Lowest Salary’
 FROM salaries
			

THE COUNT() Function Syntax:

COUNT([ALL| DISTINCT] expression)

 SELECT COUNT(*) AS ‘Number of Employees’
 FROM employees
			

The ALL keyword is assumed with aggregate functions. You don’t have to include it. This means all rows are included in the aggregate function, which is the default.

 SELECT AVG(ALL prod_price) AS ‘Average Price’
 FROM products
			

All product prices are included in the average calculation.

You can use the DISTINCT keyword instead to include only unique values.

 SELECT AVG(DISTINCT prod_price) AS ‘Average Price’
 FROM products
			

So if there are multiple products with the same price, that price will only be included once in the average calculation.

Creating Groups

In previous examples the aggregate functions summarized data across all rows in the given table. Creating groups allows you to summarize subsets of table contents.

We will use the GROUP BY clause to do this. The GROUP BY clause is used when you have an aggregate function that you want to use on the grouped data.

SELECT statement with GROUP BY and HAVING clause syntax:

SELECT select_list
FROM table_source
[WHERE search_condition]
[GROUP BY group-by_list]
[HAVING search_condition]
[ORDER BY order_by_list]

 SELECT vendor_id, COUNT(*) AS ‘Number of Products’
 FROM products 
 GROUP BY vendor_id
			

The GROUP BY clause sorts the data and groups by vendors, then the COUNT function is used to count the number of products for each vendor. So you’d have a count once per vendor rather than for the entire table.

Filtering Groups

The HAVING clause also works with aggregate functions and allows you to filter the groups (or in other words specify which groups to include or not include). It works a lot like the WHERE clause but the WHERE clause filters rows and the HAVING clause filters groups.

 SELECT vendor_id, COUNT(*) AS ‘Number of Products’
 FROM products
 GROUP BY vendor_id
 HAVING COUNT(*) > 20
			

The HAVING clause filters the vendors after they are grouped and shows only the vendors with the number of products over 20.

You can use WHERE and HAVING together. If you want to only include product prices greater than $10 and then show the number of products that result grouped by vendors for those vendors who have more than 20 products.

 SELECT vendor_id, COUNT(*) AS ‘Number of Products’
 FROM products
 WHERE product_price > 10
 GROUP BY vendor_id
 HAVING COUNT(*)  > 20
			

The ROLLUP operator can be used with grouping and aggregates allowing you to add one or more summary rows to your results. A summary row will show up for every group you have.

 SELECT vendor_id, SUM(product_price) AS ‘Total of Product Prices’
 FROM products
 WHERE vendor_id < 15
 GROUP BY vendor_id WITH ROLLUP
			

The summary row shows up as the bottom row and totals all the totals into a grand total.

SELECT Clause Syntax Order

The order of how you must write the code.

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

SELECT Order of Execution

The written order, or the syntax order of how we write a query differs from the order of how the query is actually executed. It’s important to understand the order of execution so you know what results are accessible where.

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

LIMIT

The data from the entire table(s) is retrieved, then that is filtered by the WHERE criteria. Then that resulting data is grouped and filtered by the HAVING criteria. The columns, calculations or functions that will show up are determined in SELECT. Then that resulting data is sorted and then limited.