Royal University of Phnom Penh

Faculty of Engineering

Chapter 6
How to code
summary queries
A summary query
SELECT COUNT(*) AS number_of_invoices,
SUM(invoice_total – payment_total – credit_total)
AS total_due
FROM invoices
WHERE invoice_total – payment_total – credit_total > 0
A summary query with COUNT(*), AVG, and
SUM
SELECT 'After 1/1/2018' AS selection_date,
COUNT(*) AS number_of_invoices,
ROUND(AVG(invoice_total), 2) AS avg_invoice_amt,
SUM(invoice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > '2018-01-01'
A summary query with MIN and MAX
SELECT 'After 1/1/2018' AS selection_date,
COUNT(*) AS number_of_invoices,
MAX(invoice_total) AS highest_invoice_total,
MIN(invoice_total) AS lowest_invoice_total
FROM invoices
WHERE invoice_date > '2018-01-01'
A summary query for non-numeric columns
SELECT MIN(vendor_name) AS first_vendor,
MAX(vendor_name) AS last_vendor,
COUNT(vendor_name) AS number_of_vendors
FROM vendors
A summary query with the DISTINCT keyword
SELECT COUNT(DISTINCT vendor_id) AS number_of_vendors,
COUNT(vendor_id) AS number_of_invoices,
ROUND(AVG(invoice_total), 2) AS avg_invoice_amt,
SUM(invoice_total) AS total_invoice_amt
FROM invoices
WHERE invoice_date > '2018-01-01'
A summary query that calculates
the average invoice amount by vendor
SELECT vendor_id, ROUND(AVG(invoice_total), 2)
AS average_invoice_amount
FROM invoices
GROUP BY vendor_id
HAVING AVG(invoice_total) > 2000
ORDER BY average_invoice_amount DESC

(8 rows)
A summary query that includes
a functionally dependent column
SELECT vendor_name, vendor_state,
ROUND(AVG(invoice_total), 2) AS average_invoice_amount
FROM vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name
HAVING AVG(invoice_total) > 2000
ORDER BY average_invoice_amount DESC
A summary query that counts
the number of invoices by vendor
SELECT vendor_id, COUNT(*) AS invoice_qty
FROM invoices
GROUP BY vendor_id

(34 rows)
A summary query with a join
SELECT vendor_state, vendor_city, COUNT(*) AS invoice_qty,
ROUND(AVG(invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_state, vendor_city
ORDER BY vendor_state, vendor_city

(20 rows)
A summary query that limits the groups
to those with two or more invoices
SELECT vendor_state, vendor_city, COUNT(*) AS invoice_qty,
ROUND(AVG(invoice_total), 2) AS invoice_avg
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_state, vendor_city
HAVING COUNT(*) >= 2
ORDER BY vendor_state, vendor_city

(12 rows)
A summary query with a search condition
in the HAVING clause
SELECT vendor_name,
COUNT(*) AS invoice_qty,
ROUND(AVG(invoice_total), 2) AS invoice_avg
FROM vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name
HAVING AVG(invoice_total) > 500
ORDER BY invoice_qty DESC

(19 rows)
A summary query with a search condition
in the WHERE clause
SELECT vendor_name,
COUNT(*) AS invoice_qty,
ROUND(AVG(invoice_total), 2) AS invoice_avg
FROM vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
WHERE invoice_total > 500
GROUP BY vendor_name
ORDER BY invoice_qty DESC

(20 rows)
A summary query with a compound condition
in the HAVING clause
SELECT
invoice_date,
COUNT(*) AS invoice_qty,
SUM(invoice_total) AS invoice_sum
FROM invoices
GROUP BY invoice_date
HAVING invoice_date BETWEEN '2018-05-01' AND '2018-05-31'
AND COUNT(*) > 1
AND SUM(invoice_total) > 100
ORDER BY invoice_date DESC

The result set

(7 rows)
The same query coded with a WHERE clause
SELECT
invoice_date,
COUNT(*) AS invoice_qty,
SUM(invoice_total) AS invoice_sum
FROM invoices
WHERE invoice_date BETWEEN '2018-05-01' AND '2018-05-31'
GROUP BY invoice_date
HAVING COUNT(*) > 1
AND SUM(invoice_total) > 100
ORDER BY invoice_date DESC

The same result set

(7 rows)
Roll Up &
Window
Functionality
A summary query with a final summary row
SELECT vendor_id, COUNT(*) AS invoice_count,
SUM(invoice_total) AS invoice_total
FROM invoices
GROUP BY vendor_id WITH ROLLUP

(35 rows)
A summary query with a summary row
for each grouping level
SELECT vendor_state, vendor_city, COUNT(*) AS qty_vendors
FROM vendors
WHERE vendor_state IN ('IA', 'NJ')
GROUP BY vendor_state, vendor_city WITH ROLLUP
The basic syntax of the GROUPING function
GROUPING(expression)

A summary query that uses WITH ROLLUP

on a table with null values
SELECT invoice_date, payment_date,
SUM(invoice_total) AS invoice_total,
SUM(invoice_total - credit_total - payment_total)
AS balance_due
FROM invoices
WHERE invoice_date BETWEEN '2018-07-24' AND '2018-07-31'
GROUP BY invoice_date, payment_date WITH ROLLUP
A query that substitutes literals for nulls
in summary rows
SELECT IF(GROUPING(invoice_date) = 1, 'Grand totals',
invoice_date) AS invoice_date,
IF(GROUPING(payment_date) = 1, 'Invoice date totals',
payment_date) AS payment_date,
SUM(invoice_total) AS invoice_total,
SUM(invoice_total - credit_total - payment_total)
AS balance_due
FROM invoices
WHERE invoice_date BETWEEN '2018-07-24' AND '2018-07-31'
GROUP BY invoice_date, payment_date WITH ROLLUP
A query that displays only summary rows
SELECT IF(GROUPING(invoice_date) = 1, 'Grand totals', invoice_date)
AS invoice_date,
IF(GROUPING(payment_date) = 1, 'Invoice date totals',
payment_date) AS payment_date,
SUM(invoice_total) AS invoice_total,
SUM(invoice_total - credit_total - payment_total)
AS balance_due
FROM invoices
WHERE invoice_date BETWEEN '2018-07-24' AND '2018-07-31'
GROUP BY invoice_date, payment_date WITH ROLLUP
HAVING GROUPING(invoice_date) = 1 OR GROUPING(payment_date) = 1
The basic syntax of the OVER clause
OVER([PARTITION BY expression1 [, expression2]...
[ORDER BY expression1 [ASC|DESC]
[, expression2 [ASC|DESC]]...)

A SELECT statement
with two aggregate window functions
SELECT vendor_id, invoice_date, invoice_total,
SUM(invoice_total) OVER() AS total_invoices,
SUM(invoice_total) OVER(PARTITION BY vendor_id)
AS vendor_total
FROM invoices
WHERE invoice_total > 5000
A SELECT statement with a cumulative total
SELECT vendor_id, invoice_date, invoice_total,
SUM(invoice_total) OVER() AS total_invoices,
SUM(invoice_total) OVER(PARTITION BY vendor_id
ORDER BY invoice_total) AS vendor_total
FROM invoices
WHERE invoice_total > 5000