My Web Training

📖 MySQL Summary Queries

Summary queries are used to summarize table data. They use aggregate functions to perform statistical calculations on the table data.

Aggregate Functions: returns a single row containing the result of mathematical computation on the values in a set of selected rows.
Summary Query: a select statement that includes one or more aggregate functions.

Aggregate Functions
Name	Output
AVG(column-name)	Averages the values in a column.
COUNT(column-name)	Counts the values in a column.
MAX(column-name)	Finds the largest value in a column.
MIN(column-name)	Finds the smallest value in a column.
SUM(column-name)	Totals the values in a column.

Examples

SELECT AVG(GPA) FROM Students;
-- Returns the average of the GPA column from all students.

SELECT COUNT(*) FROM Students WHERE Major = 'CIT';
-- Counts the number of rows in the Students table with a Major of 'CIT'.

SELECT COUNT(DISTINCT(studentID)) FROM Students WHERE Major = 'CIT';
-- Counts the number of rows in the Students table with a Major of 'CIT' and excludes duplicates.

SELECT MAX(age) as Oldest FROM Students;
-- Finds the largest value in age and stores it in the results table in a field called Oldest.

SELECT MIN(age), MAX(age), AVG(age) FROM Students;
-- Returns the minimum, maximum and average age of all students.

Grouping: allows you to group rows with the same value in a specific field.

SELECT Major, AVG(GPA) FROM Students GROUP BY Major ORDER BY Major;
-- Displays the average GPA of students within each Major.

GROUP BY and HAVING clauses.

Adds a conditions test to an aggregate function.

SELECT AVG(GPA) FROM Students GROUP BY Major HAVING COUNT(*) >= 20 ORDER BY Major;
-- Displays the average GPA of students within each Major for Majors with at least 20 students. 
-- Majors with fewer than 20 students are eliminated from the results.

Can be used with a WHERE clause to limit the groups of rows analyzed.

SELECT AVG(GPA) FROM Students WHERE Major IN ('CIT', 'MIS', 'CSI') GROUP BY major HAVING COUNT(*) >= 20 ORDER BY Major;

Differences between WHERE and HAVING clauses.

WHERE clauses limit the rows included in the aggregate or GROUPING function (before the aggregate function is applied).
For example if you only want orders placed in 2012 that have not been shipped, and then group the orders by type (internet, walk in, telephone, catalog etc) you must first limit the rows selected to the proper date range and shipping information using a WHERE clause.
HAVING limits the aggregate results that are displayed (after the aggregate function is applied)
A WHERE clause cannot contain an aggregate function. A HAVING clause can.
A WHERE clause can refer to any column in the table. A HAVING clause can only refer to columns included in the select statement.

WITH ROLLUP

Used in the GROUP BY clause to add summary rows to the final result

Adds a summary row for each group specified in the GROUP BY clause
Also adds a summary row to the end of the result set that summarizes the entire result set
If the GROUP BY clause specifies a single group, only 1 final summary row is added
When you use WITH ROLLUP, you cannot use ORDER BY or DISTINCT

SELECT 
    State, COUNT(CustomerId)
FROM
    customer
WHERE
    State IS NOT NULL AND Country = 'USA'
GROUP BY State WITH ROLLUP;

Query Results
State	COUNT(CustomerId)
AZ	1
CA	3
FL	1
IL	1
MA	1
NV	1
NY	1
TX	1
UT	1
WA	1
WI	1
NULL	13