SQL to count rows per group
Count how many rows fall into each category with GROUP BY.
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;SELECT customer_id, COUNT(DISTINCT product_id) AS products_bought
FROM orders
GROUP BY customer_id;COUNT(DISTINCT col) counts unique values; COUNT(*) counts all rows including repeats.
How to count rows per group
Counting per group is the most common aggregation: group by the category column and count the rows in each group.
How it works
- GROUP BY status creates one group per distinct status value.
- COUNT(*) counts every row in the group; COUNT(col) ignores NULLs in that column.
- Every non-aggregated column in the SELECT must appear in GROUP BY.
Related SQL queries
- SQL to group by month
- SQL to calculate a running total
- SQL to pivot rows into columns
- SQL to find duplicate rows
- SQL to delete duplicate rows (keep one)
- SQL to find the second highest value
Frequently asked questions
How do I count rows per group in SQL?
Counting per group is the most common aggregation: group by the category column and count the rows in each group. GROUP BY status creates one group per distinct status value. COUNT(*) counts every row in the group; COUNT(col) ignores NULLs in that column. Every non-aggregated column in the SELECT must appear in GROUP BY.
Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?
Yes — this page lists the query for each dialect, since the syntax can differ between database engines.
Can I generate this query for my own tables?
Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.