SQL Intermediate Cheatsheet — Aggregate Functions

Aggregate Functions

COUNT

SELECT COUNT(*)
FROM table_name
SELECT COUNT(user_ID)
FROM table_name
SELECT COUNT(DISTINCT user_ID)
FROM table_name

SUM

SELECT SUM(volume)
FROM apple_stock_price

GROUP BY

SELECT year, month, SUM(volume)
FROM apple_stock_price
GROUP BY year, month
ORDER BY year, month
SELECT product, COUNT(*)
FROM transactions
GROUP BY product

HAVING

SELECT year, month, MAX(price)
FROM apple_stock_price
GROUP BY year, month
HAVING MAX(price) > 400
SELECT product, AVG(price)
FROM transactions
GROUP BY product
HAVING AVG(price) <= 200

CASE WHEN

SELECT name, school_year, CASE WHEN school_year IN ('senior', 'junior') THEN 'yes' ELSE NULL END AS is_senior 
FROM ncaa_players
SELECT CASE WHEN state IN ('CA', 'WA', 'OR') THEN 'west coast' WHEN state = 'TX' THEN 'texas' ELSE 'other' END AS region, COUNT(1) AS player_count
FROM ncaa_players
WHERE weight > 200
GROUP BY 1
SELECT state, COUNT(CASE WHEN year = 'junior' THEN 1 ELSE NULL END) AS junior_count, COUNT(CASE WHEN year = 'senior' THEN 1 ELSE NULL END) AS senior_count, COUNT(1) AS total_players_by_state
FROM ncaa_players
GROUP BY state
ORDER BY total_players_by_state DESC

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store