sql
SELECT COUNT(*) AS total_users FROM users;Notes
A basic but essential query for dashboards and sanity checks.
MySQL Aggregation and Window Functions Cheatsheet
group by, having, count, sum, avg, row_number, lag, running totals, and analytical query patterns in MySQL.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
aggregation and grouping
Summaries, counts, totals, averages, and grouped reporting queries.
sql
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;Notes
A common reporting pattern for order states, ticket states, and similar grouped data.
sql
SELECT SUM(total_amount) AS gross_revenue, AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'paid';Notes
Numeric aggregates are core to finance and analytics queries.
Filter grouped results with HAVING
Keep only groups that meet aggregate criteria.
sqlANYhavinggroup-by
sql
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;Notes
`HAVING` filters after aggregation, unlike `WHERE`.
window functions
Ranking, running totals, and partition-aware analytics.
Assign row numbers within each user
Rank a user's orders by recency.
sqlANYwindowrow_numberpartition
sql
SELECT
user_id,
id AS order_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;Notes
Helpful for picking latest rows per entity.
sql
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_order_metrics;Notes
Window functions let you keep row-level detail while adding cumulative calculations.
sql
SELECT
order_date,
daily_revenue,
daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS delta
FROM daily_order_metrics;Notes
`LAG` is excellent for trend analysis and time-series deltas.
sql
SELECT
category,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM category_revenue;Notes
Unlike `RANK`, `DENSE_RANK` does not skip numbers after ties.
More in MySQL
MySQL Procedures, Triggers, and Events Cheatsheet
stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
MySQL Functions, JSON, and Date Cheatsheet
now, date_add, date_sub, date_format, timestampdiff, json_extract, json_set, json_object, concat, coalesce, and case in MySQL.
MySQL Backup, Restore, Import, and Export Cheatsheet
mysqldump, mysql restore, load data infile, select into outfile, mysqlimport, and MySQL Shell dump/load workflows.
MySQL Users, Grants, and Privileges Cheatsheet
create users, alter passwords, grant privileges, show grants, revoke access, and manage MySQL accounts safely.
MySQL Transactions and Locking Cheatsheet
start transaction, commit, rollback, savepoints, select for update, isolation levels, and InnoDB lock troubleshooting.
MySQL Indexes, EXPLAIN, and Performance Cheatsheet
index design, show index, explain, explain analyze, analyze table, and practical performance tuning workflows in MySQL.