sqlANYcountaggregate
sql
SELECT COUNT(*) AS total_users FROM users;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.
sqlANYgroup-bycountstatus
sql
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;A common reporting pattern for order states, ticket states, and similar grouped data.
sqlANYsumavgrevenue
sql
SELECT SUM(total_amount) AS gross_revenue, AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'paid';Numeric aggregates are core to finance and analytics queries.
sqlANYhavinggroup-by
sql
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;`HAVING` filters after aggregation, unlike `WHERE`.
window functions
Ranking, running totals, and partition-aware analytics.
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;Helpful for picking latest rows per entity.
sqlANYwindowrunning-total
sql
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_order_metrics;Window functions let you keep row-level detail while adding cumulative calculations.
sqlANYwindowlaganalytics
sql
SELECT
order_date,
daily_revenue,
daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS delta
FROM daily_order_metrics;`LAG` is excellent for trend analysis and time-series deltas.
sqlANYwindowdense_rank
sql
SELECT
category,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM category_revenue;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.