GROUP BY in SQL

GROUP BY is where SQL stops listing rows and starts answering business questions. "How many employees per city?" "What is the average salary per department?" "Which department has the highest total payroll?" Every one of those is a GROUP BY query paired with an aggregate function.

If reporting, dashboards or a data analyst role interest you, this is the most important tutorial in the series. It builds directly on filtering and sorting from the SELECT statement in SQL, so make sure WHERE and ORDER BY feel comfortable first.

Our sample data

-- employees
emp_id | emp_name | department_id | salary | city
-------+----------+---------------+--------+-----------
101    | Aarti    | 1             | 78000  | Hyderabad
102    | Bhaskar  | 1             | 65000  | Hyderabad
103    | Chitra   | 2             | 52000  | Bengaluru
104    | Devan    | 2             | 58000  | Bengaluru
105    | Esha     | 3             | 47000  | Hyderabad
106    | Farhan   | 4             | 90000  | Pune

Aggregate functions: turning many rows into one number

Before grouping, understand the five aggregate functions that do the actual summarizing. Each takes a column of many values and returns a single value:

SELECT COUNT(*)     AS total_employees,
       SUM(salary)  AS total_payroll,
       AVG(salary)  AS avg_salary,
       MIN(salary)  AS lowest_salary,
       MAX(salary)  AS highest_salary
FROM employees;

Run against all six rows, this returns one summary row: six employees, a total payroll, an average, and the salary range. Without GROUP BY, an aggregate treats the entire table as a single group.

Pro tip: COUNT(*) counts every row, while COUNT(salary) counts only rows where salary is not NULL. When a column can be missing, choose deliberately — the two counts can differ and interviewers love to probe whether you know why.

Adding GROUP BY

GROUP BY splits the table into groups that share a value, then runs the aggregate on each group separately. To get a count per city:

SELECT city, COUNT(*) AS headcount
FROM employees
GROUP BY city;

Result:

city      | headcount
----------+----------
Hyderabad | 3
Bengaluru | 2
Pune      | 1

The database found the three distinct cities, gathered the matching rows into each group, and counted them. Swap COUNT(*) for AVG(salary) and you get the average salary per city instead — same grouping, different question.

SELECT city, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY city;

The rule that causes most GROUP BY errors

Here is the rule to memorize: every column in your SELECT must either appear in GROUP BY or be inside an aggregate function. Break it and the database rejects the query.

-- ERROR: emp_name is neither grouped nor aggregated
SELECT city, emp_name, COUNT(*)
FROM employees
GROUP BY city;

Once rows are collapsed into a city group, there are three different emp_name values for Hyderabad — the database cannot pick one. Either group by the column too, or aggregate it away:

-- valid: emp_name is now part of the grouping key
SELECT city, department_id, COUNT(*) AS headcount
FROM employees
GROUP BY city, department_id;

Common mistake: Selecting a plain column that is not in the GROUP BY clause. The mental model that fixes this: after GROUP BY runs, each group is one output row, so any column you show must have exactly one value per group — which means it is either a grouping key or an aggregate.

Grouping by multiple columns

GROUP BY city, department_id creates one group per unique combination of city and department. This is how cross-tabulated reports are built — headcount broken down by two dimensions at once. The more columns you group by, the finer and more numerous your groups become.

Filtering groups with HAVING

WHERE filters rows before grouping. But what if you want to filter the groups themselves — for example, "only cities with more than one employee"? You cannot use WHERE for that, because the count does not exist until after grouping. That is what HAVING is for.

SELECT city, COUNT(*) AS headcount
FROM employees
GROUP BY city
HAVING COUNT(*) > 1;

Result drops Pune, leaving Hyderabad and Bengaluru. The distinction between WHERE and HAVING is one of the most tested points in SQL interviews:

You often use both in one query — WHERE to discard irrelevant rows first, then HAVING to keep only the groups you care about:

SELECT city, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 40000          -- filter rows first
GROUP BY city
HAVING AVG(salary) > 50000    -- then filter groups
ORDER BY avg_salary DESC;

Interview note: Expect "What is the difference between WHERE and HAVING?" in almost every SQL interview. The crisp answer: WHERE filters rows before aggregation and cannot use aggregate functions; HAVING filters groups after aggregation and can. Mention the execution order to show depth.

Combining GROUP BY with joins

Grouping becomes far more useful once you join tables. To get the total payroll per department name (not just id), join employees to departments first, then group:

SELECT d.department_name,
       COUNT(e.emp_id)  AS headcount,
       SUM(e.salary)    AS total_payroll
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY total_payroll DESC;

The LEFT JOIN ensures a department with zero employees still shows up with a headcount of 0. This join-then-group pattern is the backbone of nearly every dashboard. If joins are still shaky, revisit SQL joins explained.

Counting distinct values

A common analytical need is counting unique values, not total rows. COUNT(DISTINCT column) does exactly that. To count how many distinct departments have employees in each city:

SELECT city,
       COUNT(*)                     AS employees,
       COUNT(DISTINCT department_id) AS departments
FROM employees
GROUP BY city;

For Hyderabad, COUNT(*) is 3 but COUNT(DISTINCT department_id) is 2, because Aarti and Bhaskar share department 1. This distinction — total rows versus distinct values — is the source of many subtle reporting bugs, so name in your own head which one a question is really asking.

Building a real report step by step

Let us assemble a report the way you would at work: "For each city, show headcount, average salary rounded to whole rupees, and the highest earner's salary, but only for cities with at least two employees, sorted by average salary." Reading a plain-English requirement and turning it into layered clauses is the actual skill interviews test.

SELECT city,
       COUNT(*)              AS headcount,
       ROUND(AVG(salary), 0) AS avg_salary,
       MAX(salary)           AS top_salary
FROM employees
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

Trace it through the execution order: FROM reads the table, GROUP BY forms one group per city, the aggregates compute per group, HAVING drops single-employee cities, and ORDER BY sorts the survivors. Every analytical query you write for the rest of your career is a variation on this shape. Practicing the translation from requirement to query is far more valuable than memorizing syntax.

Pro tip: Build grouped queries incrementally. Start with the plain GROUP BY and one aggregate, confirm the numbers look right, then add HAVING, then ORDER BY. Debugging a five-clause query all at once is far harder than growing it one clause at a time.

GROUP BY versus window functions

GROUP BY collapses rows — six employees become three city rows, and you lose the individual detail. Sometimes you want the summary alongside each original row, such as showing every employee next to their department's average salary. That is a job for window functions, not GROUP BY, and it is the natural next topic in SQL window functions introduction.

Where to go next

GROUP BY and the aggregate functions are the core of analytical SQL. With them you can answer counting, totaling and averaging questions across any dimension — the daily bread of reporting work.

Practice by rewriting business questions from your own domain into GROUP BY queries. Then explore how to keep row-level detail with window functions, and test your grouping skills against the SQL interview questions for freshers. Strong grouping skills are also central to the data analyst roadmap.