Summarizing data Flashcards
What are the aggregate functions in sql?
MAX, MIN, AVG, SUM, COUNT
What is this query returning?
SELECT MAX(invoice\_total) FROM invoices
the largest invoice total on the invoices table
What is this sql query returning?
the max, min, and avg values as highest, lowest, average of the invoice total on the invoices table
True or False
Aggregate functions can be placed on non-numeric columns as well?
True like a string date
In this query, payment date is referring to what?
the latest payment date
In this query the SUM line is returning what?
summing the invoice_total for the column
In this query the COUNT line is returning what?
the total number of invoices on the table
True or False
Aggregate functions will operate on NULL values?
False
NULL values will be ignored
COUNT(*) returns what?
ALL records, NULL or not
What is the order of operations on this line of code?
SUM(invoice_total * 1.1) AS total
parenthesis first
each row of invoice_total will be multiplied by 1.1 then SUM will be applied.
True or False
Because of the WHERE clause each of the agg functions will be calculated on every row on the invoices table
False
Only those matching the WHERE clause, filtering the number of records being returned
If you want to COUNT unique values (like cleint_id) what statement must also be used?
DISTINCT
Query this result
same SELECT statments with three UNION statements altering the date range in the BETWEEN clause
How many rows are being returned by these multiple SELECT, UNION statements?
3 rows are being queried here.
What is the issue with this query?
the total_sales haven’t been calculated before GROUP BY, use the HAVING statement
How many select and union statements will return this table?
3 SELECT statements
joined by 2 UNION statements
SELECT
‘First half of 2019’ AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expected
FROM invoices
WHERE invoice_date
BETWEEN ‘2019-01-01’ AND ‘2019-06-30’ – first half or year
UNION…(cont)
True or False
In sql the HAVING statement must follow the GROUP BY statement
True
In this query what is the HAVING statement accomplishing?
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500
It is singling out the rows greater than 500 of the total_sales column
True or False
WHERE clause filters data before the rows are grouped?
WHERE
GROUP BY
HAVING
True
True or False
HAVING clause filters data after the rows are grouped?
WHERE
GROUP BY
HAVING
True
True or False
Using the HAVING clause requires that the column is also present in the SELECT clause
True
Use the sql.store database to complete this query
- Get the customers
- located in Virginia
- who have spent more than $100
USE sql_store;
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_price
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE state =’VA’
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_price > 100
In this sql query, what is the WITH ROLLUP clause performing
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
It’s summing up the total_sales column
True or False
The WITH ROLLUP clause only applies to aggregated values not something like a customer_id
True
notice client_id is NULL
In this query the WITH ROLLUP clause is not only summing the total_sales but is also summing total_sales based on?
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP
city and state
Exercise
write a query to produce this report
SELECT
pm.name AS payment_method,
SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP
