In the Aggregate Functions chapter, functions like COUNT(), SUM() and AVG() squeezed a whole table (or all rows matching a WHERE) into one single row. That answers questions like "what is the total salary bill?". But managers usually ask a slightly different question: "what is the salary bill of each department?", "how many orders per status?", "which customers spent more than 5,000?".
For these "per something" questions we need GROUP BY, and to filter the resulting groups we need HAVING. Together they turn SQL into a small reporting engine, and they appear in almost every SQL interview.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.
What is GROUP BY?
GROUP BY splits the rows of a table into groups, where all rows in a group have the same value in the grouping column(s). Aggregate functions are then computed separately for each group, and the query returns one row per group.
SELECT grouping_column, AGGREGATE(some_column)
FROM table_name
[WHERE row_condition]
GROUP BY grouping_column
[HAVING group_condition]
[ORDER BY ...];Let's count employees in each department:
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
ORDER BY dept_id;+---------+-----------+
| dept_id | emp_count |
+---------+-----------+
| NULL | 1 |
| 10 | 3 |
| 20 | 3 |
| 30 | 2 |
| 40 | 1 |
+---------+-----------+
5 rows in set (0.00 sec)Ten employee rows went in, five rows came out: one per distinct dept_id.
How GROUP BY Works: The Bucket Picture
The easiest way to think about GROUP BY is sorting rows into buckets. MySQL looks at the grouping column of each row and drops the row into the bucket for that value. Then each aggregate function runs once per bucket.
Step 1: put every row into a bucket by dept_id
bucket 10 : Aarav (150000), Priya (95000), Rohan (95000)
bucket 20 : Sneha (110000), Vikram (62000), Ananya (58000)
bucket 30 : Karan (70000), Meera (48000)
bucket 40 : Arjun (82000)
bucket NULL : Ishita (55000)
Step 2: run the aggregates once per bucket
bucket 10 : COUNT(*) = 3 SUM(salary) = 340000
bucket 20 : COUNT(*) = 3 SUM(salary) = 230000
bucket 30 : COUNT(*) = 2 SUM(salary) = 118000
bucket 40 : COUNT(*) = 1 SUM(salary) = 82000
bucket NULL : COUNT(*) = 1 SUM(salary) = 55000
Step 3: output ONE row per bucketNote what does not appear: department 50 (Research). GROUP BY only builds buckets from rows that exist in the table. Research has no employees, so there is no bucket for it. Showing "Research: 0" needs a join with the departments table, which we will do in the LEFT, RIGHT & FULL OUTER JOIN chapter.
You can use as many aggregates as you like, and all of them are computed per group:
SELECT dept_id,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;+---------+-----------+--------------+---------------+------------+------------+
| dept_id | emp_count | total_salary | avg_salary | min_salary | max_salary |
+---------+-----------+--------------+---------------+------------+------------+
| NULL | 1 | 55000.00 | 55000.000000 | 55000.00 | 55000.00 |
| 10 | 3 | 340000.00 | 113333.333333 | 95000.00 | 150000.00 |
| 20 | 3 | 230000.00 | 76666.666667 | 58000.00 | 110000.00 |
| 30 | 2 | 118000.00 | 59000.000000 | 48000.00 | 70000.00 |
| 40 | 1 | 82000.00 | 82000.000000 | 82000.00 | 82000.00 |
+---------+-----------+--------------+---------------+------------+------------+
5 rows in set (0.00 sec)NULL Forms Its Own Group
Ishita has no department, and she was not thrown away; she got her own NULL group. This is an interesting exception to the usual NULL rules. In a WHERE clause, NULL = NULL is UNKNOWN, but for grouping (just like for DISTINCT) all NULLs are treated as one value and land in the same bucket.
Another example from the orders table, where order 1008 is a guest order with a NULL customer_id:
SELECT customer_id,
COUNT(*) AS num_orders,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;+-------------+------------+-------------+
| customer_id | num_orders | total_spent |
+-------------+------------+-------------+
| 101 | 3 | 9300.00 |
| 102 | 2 | 7900.00 |
| 103 | 1 | 7300.00 |
| 104 | 1 | 950.00 |
| NULL | 1 | 800.00 |
+-------------+------------+-------------+
5 rows in set (0.00 sec)Grouping by Multiple Columns
When you list several columns, a group is formed for each distinct combination of their values. Here we count employees per department and city:
SELECT dept_id, city, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id, city
ORDER BY dept_id, city;+---------+-----------+-----------+
| dept_id | city | emp_count |
+---------+-----------+-----------+
| NULL | NULL | 1 |
| 10 | Bengaluru | 2 |
| 10 | Chennai | 1 |
| 20 | Kolkata | 1 |
| 20 | Mumbai | 2 |
| 30 | Delhi | 1 |
| 30 | Kochi | 1 |
| 40 | Hyderabad | 1 |
+---------+-----------+-----------+
8 rows in set (0.00 sec)Department 10 is now split into two groups: (10, Bengaluru) with Aarav and Rohan, and (10, Chennai) with Priya. The order of columns inside GROUP BY does not change which groups are formed; GROUP BY city, dept_id produces the same 8 groups.
Grouping by an Expression
You can group by a computed value, not only a plain column. How many people were hired each year? (YEAR(d) and MONTH(d) simply extract the year or month number from a date; the Date & Time Functions chapter covers them and many more.)
SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY hire_year
ORDER BY hire_year;+-----------+-------+
| hire_year | hires |
+-----------+-------+
| 2017 | 1 |
| 2018 | 1 |
| 2019 | 2 |
| 2020 | 2 |
| 2021 | 1 |
| 2022 | 1 |
| 2023 | 1 |
| 2024 | 1 |
+-----------+-------+
8 rows in set (0.00 sec)Notice that we wrote GROUP BY hire_year, using the alias from the SELECT list. MySQL allows this as an extension. The SQL standard (and databases such as SQL Server) require you to repeat the expression: GROUP BY YEAR(hire_date). If you want portable SQL, repeat the expression.
A more business-like report: monthly orders and revenue, ignoring cancelled orders.
SELECT MONTH(order_date) AS month_no,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY month_no
ORDER BY month_no;+----------+--------+---------+
| month_no | orders | revenue |
+----------+--------+---------+
| 1 | 2 | 7300.00 |
| 2 | 1 | 7300.00 |
| 3 | 3 | 9650.00 |
| 4 | 1 | 800.00 |
+----------+--------+---------+
4 rows in set (0.00 sec)(All our orders are from 2024. With data spanning several years you would group by both YEAR(order_date) and MONTH(order_date), otherwise January 2024 and January 2025 would fall into the same bucket.)
MySQL also accepts column positions, like GROUP BY 1 meaning "the first column of the SELECT list". It works, but it breaks silently when someone reorders the SELECT list, so avoid it in real code.
The Golden Rule: What Can Go in the SELECT List?
After grouping, each output row represents a whole bucket, not a single employee. So every item in the SELECT list must have exactly one value per bucket. That leaves only:
the grouping columns (same value for every row in the bucket, by definition),
aggregate functions (they collapse the bucket into one value),
constants and expressions built from the two above.
What happens if we ask for name per department? Bucket 10 has three names. Which one should MySQL print?
SELECT dept_id, name, COUNT(*)
FROM employees
GROUP BY dept_id;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'company_db.employees.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byMySQL refuses, because the question has no single answer. This check is controlled by the ONLY_FULL_GROUP_BY SQL mode, which is on by default since MySQL 5.7.5. Very old MySQL versions (and servers where someone switched this mode off) would silently print an arbitrary name from the group. Many old tutorials and Stack Overflow answers rely on that behaviour; it is a bug waiting to happen.
Functional Dependency: When MySQL Allows It
The error message mentions "functionally dependent". If you group by a column that uniquely identifies the row, such as the primary key, then every other column of that table has only one value per group, and MySQL knows it:
SELECT emp_id, name, salary
FROM employees
GROUP BY emp_id
LIMIT 3;+--------+--------------+-----------+
| emp_id | name | salary |
+--------+--------------+-----------+
| 1 | Aarav Sharma | 150000.00 |
| 2 | Priya Iyer | 95000.00 |
| 3 | Rohan Mehta | 95000.00 |
+--------+--------------+-----------+
3 rows in set (0.00 sec)This becomes genuinely useful with joins: grouping by d.dept_id (the primary key of departments) lets you also select d.dept_name without listing it in GROUP BY.
ANY_VALUE(): "I Know, Just Pick One"
If you truly do not care which value is shown, wrap the column in ANY_VALUE(). It tells MySQL to skip the check and return some value from the group.
SELECT dept_id, ANY_VALUE(name) AS some_employee, COUNT(*)
FROM employees
GROUP BY dept_id
ORDER BY dept_id;+---------+---------------+----------+
| dept_id | some_employee | COUNT(*) |
+---------+---------------+----------+
| NULL | Ishita Gupta | 1 |
| 10 | Aarav Sharma | 3 |
| 20 | Sneha Kapoor | 3 |
| 30 | Karan Patel | 2 |
| 40 | Arjun Reddy | 1 |
+---------+---------------+----------+
5 rows in set (0.00 sec)The value is not guaranteed (here it happened to be the first row stored). Never use ANY_VALUE to answer "who is the highest-paid employee in each department"; that needs a subquery or a window function (see the Window Functions chapter).
COUNT Variants Inside Groups
The three forms of COUNT behave per group exactly as they do on a whole table:
SELECT dept_id,
COUNT(*) AS total_rows,
COUNT(email) AS with_email,
COUNT(DISTINCT salary) AS distinct_salaries
FROM employees
GROUP BY dept_id
ORDER BY dept_id;+---------+------------+------------+-------------------+
| dept_id | total_rows | with_email | distinct_salaries |
+---------+------------+------------+-------------------+
| NULL | 1 | 1 | 1 |
| 10 | 3 | 3 | 2 |
| 20 | 3 | 3 | 3 |
| 30 | 2 | 1 | 2 |
| 40 | 1 | 1 | 1 |
+---------+------------+------------+-------------------+
5 rows in set (0.00 sec)Department 30 has 2 rows but only 1 email, because Meera's email is NULL and
COUNT(email)skips NULLs.Department 10 has 3 rows but only 2 distinct salaries, because Priya and Rohan both earn 95,000.
HAVING: Filtering Groups
Suppose we only want departments with at least 2 employees. The count is not known until the groups have been built, so we cannot put it in WHERE. That is what HAVING is for.
HAVING filters groups after GROUP BY has formed them. Its condition usually uses aggregate functions. Only groups for which the condition is TRUE appear in the result.
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY dept_id;+---------+-----------+
| dept_id | emp_count |
+---------+-----------+
| 10 | 3 |
| 20 | 3 |
| 30 | 2 |
+---------+-----------+
3 rows in set (0.00 sec)Departments with an average salary above 80,000:
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 80000
ORDER BY avg_salary DESC;+---------+---------------+
| dept_id | avg_salary |
+---------+---------------+
| 10 | 113333.333333 |
| 40 | 82000.000000 |
+---------+---------------+
2 rows in set (0.00 sec)The HAVING condition does not even have to appear in the SELECT list. "Customers who placed more than one order":
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY customer_id;+-------------+
| customer_id |
+-------------+
| 101 |
| 102 |
+-------------+
2 rows in set (0.00 sec)WHERE vs HAVING
This is the single most asked GROUP BY interview question. The key idea is when each filter runs:
all rows
|
v
WHERE -- filters individual ROWS (no groups exist yet)
|
v
GROUP BY -- builds the buckets
|
v
HAVING -- filters whole GROUPS (aggregates are now known)
|
v
resultLet's use both in one query: "count employees earning more than 60,000 in each department, and keep only departments where at least 2 such employees exist".
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 60000
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY dept_id;+---------+-----------+
| dept_id | emp_count |
+---------+-----------+
| 10 | 3 |
| 20 | 2 |
+---------+-----------+
2 rows in set (0.00 sec)Trace it step by step:
1. WHERE salary > 60000 removes rows:
Ananya (58000), Meera (48000), Ishita (55000) -> dropped
7 rows survive
2. GROUP BY dept_id on the 7 surviving rows:
10 : Aarav, Priya, Rohan -> COUNT = 3
20 : Sneha, Vikram -> COUNT = 2
30 : Karan -> COUNT = 1
40 : Arjun -> COUNT = 1
(the NULL group never forms: Ishita was removed in step 1)
3. HAVING COUNT(*) >= 2:
keeps 10 and 20; drops 30 and 40Compare this with the earlier HAVING-only query, where department 20 had 3 employees and department 30 survived. The WHERE changed what was counted.
Trying to use an aggregate in WHERE fails, because at WHERE time there are no groups to count:
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
WHERE COUNT(*) >= 2
GROUP BY dept_id;ERROR 1111 (HY000): Invalid use of group functionPoint | WHERE | HAVING |
|---|---|---|
Filters | Individual rows | Groups |
Runs | Before GROUP BY | After GROUP BY |
Can use aggregates (COUNT, SUM...)? | No | Yes |
Works without GROUP BY? | Yes (most common use) | Yes, the whole result is then treated as one group |
Also used in UPDATE / DELETE? | Yes | No |
Typical example |
|
|
Non-Aggregate Conditions: Put Them in WHERE
HAVING can also filter on a grouping column, and you get the same answer:
SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id
HAVING dept_id IN (10, 20);+---------+----------+
| dept_id | COUNT(*) |
+---------+----------+
| 10 | 3 |
| 20 | 3 |
+---------+----------+
2 rows in set (0.00 sec)But the better version is WHERE dept_id IN (10, 20). WHERE throws rows away before grouping, so the database groups less data and can use an index on dept_id. With HAVING it groups everything first and discards most groups afterwards. Rule of thumb: if a condition does not need an aggregate, it belongs in WHERE.
A Realistic Example
"Customers whose delivered orders add up to more than 5,000." The status check is about individual orders (WHERE); the total is about the group (HAVING).
SELECT customer_id, SUM(amount) AS delivered_total
FROM orders
WHERE status = 'Delivered'
GROUP BY customer_id
HAVING SUM(amount) > 5000
ORDER BY delivered_total DESC;+-------------+-----------------+
| customer_id | delivered_total |
+-------------+-----------------+
| 102 | 7900.00 |
| 103 | 7300.00 |
+-------------+-----------------+
2 rows in set (0.00 sec)Customer 101 spent 9,300 in total, but only 2,500 of it was delivered (1,200 was cancelled and 5,600 is pending), so 101 does not qualify.
Aliases in HAVING
MySQL lets HAVING refer to a SELECT alias:
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING emp_count >= 2
ORDER BY dept_id;+---------+-----------+
| dept_id | emp_count |
+---------+-----------+
| 10 | 3 |
| 20 | 3 |
| 30 | 2 |
+---------+-----------+
3 rows in set (0.00 sec)This is a MySQL convenience. Standard SQL, PostgreSQL and SQL Server do not allow it; there you must write HAVING COUNT(*) >= 2. In exams and interviews, writing the aggregate again is the safe choice.
Logical Order of Execution
Now we can complete the processing order you first saw in the SELECT Statement chapter:
Step | Clause | What happens |
|---|---|---|
1 | FROM | Pick the source table(s) |
2 | WHERE | Drop rows that fail the condition |
3 | GROUP BY | Form groups |
4 | HAVING | Drop groups that fail the condition |
5 | SELECT | Compute the output columns and aliases |
6 | DISTINCT | Remove duplicate output rows |
7 | ORDER BY | Sort |
8 | LIMIT | Keep only the requested number of rows |
This order explains most rules in this chapter: WHERE cannot see aggregates (step 2 is before step 3), ORDER BY can use aliases (step 7 is after step 5). MySQL's acceptance of aliases in GROUP BY and HAVING is a special extension on top of this model, not a contradiction of it. The optimizer may physically execute things differently, but the result is always as if this order was followed.
GROUP BY and ORDER BY
In MySQL 5.7 and earlier, GROUP BY also sorted the result as a side effect, and lots of code depended on it. MySQL 8.0 removed this implicit sorting (and the old GROUP BY col DESC syntax). Groups now come out in whatever order the chosen algorithm produces. Look at this query without ORDER BY:
SELECT city, COUNT(*)
FROM customers
GROUP BY city;+-----------+----------+
| city | COUNT(*) |
+-----------+----------+
| Delhi | 1 |
| Bengaluru | 1 |
| Pune | 1 |
| Mumbai | 1 |
| NULL | 1 |
+-----------+----------+
5 rows in set (0.00 sec)Not alphabetical. If order matters, always write ORDER BY. You can sort by an aggregate too, which together with LIMIT gives "top N groups":
SELECT status, COUNT(*) AS num_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY num_orders DESC, status
LIMIT 2;+-----------+------------+--------------+
| status | num_orders | total_amount |
+-----------+------------+--------------+
| Delivered | 4 | 17700.00 |
| Pending | 2 | 6400.00 |
+-----------+------------+--------------+
2 rows in set (0.00 sec)Classic Use: Finding Duplicates
"Find values that appear more than once" is a favourite interview question, and GROUP BY + HAVING solves it in one line of logic. Which salaries are shared by more than one employee?
SELECT salary, COUNT(*) AS how_many
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;+----------+----------+
| salary | how_many |
+----------+----------+
| 95000.00 | 2 |
+----------+----------+
1 row in set (0.00 sec)The same pattern finds duplicate emails, duplicate phone numbers, or rows that violate a uniqueness rule before you add a UNIQUE constraint.
GROUP BY vs DISTINCT
Without aggregates, GROUP BY and DISTINCT return the same set of values:
SELECT DISTINCT dept_id FROM employees ORDER BY dept_id;+---------+
| dept_id |
+---------+
| NULL |
| 10 |
| 20 |
| 30 |
| 40 |
+---------+
5 rows in set (0.00 sec)SELECT dept_id FROM employees GROUP BY dept_id ORDER BY dept_id returns exactly the same 5 rows. Use DISTINCT when you only want unique values, and GROUP BY when you want to compute something per group. Choosing the one that expresses your intent makes queries easier to read.
Subtotals with WITH ROLLUP
Reports often need a grand total row under the per-group rows. MySQL's WITH ROLLUP modifier adds it:
SELECT dept_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id WITH ROLLUP;+---------+-----------+--------------+
| dept_id | emp_count | total_salary |
+---------+-----------+--------------+
| NULL | 1 | 55000.00 |
| 10 | 3 | 340000.00 |
| 20 | 3 | 230000.00 |
| 30 | 2 | 118000.00 |
| 40 | 1 | 82000.00 |
| NULL | 10 | 825000.00 |
+---------+-----------+--------------+
6 rows in set (0.00 sec)The last row is the super-aggregate over all 10 employees. Its dept_id is shown as NULL, which is confusing here because the first row is also NULL (Ishita's real "no department" group). The GROUPING() function (MySQL 8.0+) tells them apart: it returns 1 only on the rollup row.
SELECT IF(GROUPING(dept_id), 'ALL DEPTS', dept_id) AS dept,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id WITH ROLLUP;+-----------+-----------+--------------+
| dept | emp_count | total_salary |
+-----------+-----------+--------------+
| NULL | 1 | 55000.00 |
| 10 | 3 | 340000.00 |
| 20 | 3 | 230000.00 |
| 30 | 2 | 118000.00 |
| 40 | 1 | 82000.00 |
| ALL DEPTS | 10 | 825000.00 |
+-----------+-----------+--------------+
6 rows in set (0.00 sec)With several grouping columns, ROLLUP adds a subtotal at each level (for example per department, then a grand total). The SQL standard spells this GROUP BY ROLLUP(dept_id). MySQL's traditional spelling is WITH ROLLUP, which works in every version; MySQL 8 also accepts the standard ROLLUP(...) form (we checked it on 8.4, it gives the same rows).
GROUP_CONCAT: Listing a Group's Members
Sometimes you want the members of each group, not just a number. MySQL's GROUP_CONCAT() is an aggregate that joins the group's values into one string:
SELECT dept_id,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS team
FROM employees
GROUP BY dept_id
ORDER BY dept_id;+---------+----------------------------------------+
| dept_id | team |
+---------+----------------------------------------+
| NULL | Ishita Gupta |
| 10 | Aarav Sharma, Priya Iyer, Rohan Mehta |
| 20 | Ananya Das, Sneha Kapoor, Vikram Singh |
| 30 | Karan Patel, Meera Nair |
| 40 | Arjun Reddy |
+---------+----------------------------------------+
5 rows in set (0.00 sec)Two things to know: the result is cut off at group_concat_max_len bytes (1024 by default, with only a warning), and other databases call this function differently (STRING_AGG in PostgreSQL and SQL Server, LISTAGG in Oracle).
HAVING Without GROUP BY, and Nested Aggregates
If a query has aggregates and HAVING but no GROUP BY, the whole (filtered) table is one group. The query returns one row if the condition holds and zero rows otherwise:
SELECT COUNT(*) FROM employees HAVING COUNT(*) > 5;+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)A common wish is "the highest department average": MAX(AVG(salary)). MySQL does not allow an aggregate directly inside another aggregate:
SELECT MAX(AVG(salary)) FROM employees GROUP BY dept_id;ERROR 1111 (HY000): Invalid use of group functionThe fix is to compute the averages first and aggregate over that result, using a subquery or a CTE (covered in the Subqueries and Common Table Expressions chapters), or simply ORDER BY AVG(salary) DESC LIMIT 1 when you only need the top group.
Common Mistakes
Selecting a non-grouped column.
SELECT dept_id, name ... GROUP BY dept_idraises error 1055. Either add the column to GROUP BY, aggregate it, or rethink the question.Using an aggregate in WHERE.
WHERE COUNT(*) > 1is error 1111; move it to HAVING.Putting row filters in HAVING. It works but wastes effort; filter rows in WHERE.
Relying on GROUP BY to sort. Since MySQL 8.0 it does not; add ORDER BY.
Forgetting the NULL group. Rows with NULL in the grouping column form their own group; filter them out with
WHERE col IS NOT NULLif you do not want them.Expecting empty groups. GROUP BY never produces a group with 0 rows; departments without employees need an outer join.
Grouping by month only across years. Group by year and month.
Using ANY_VALUE to "fix" the error when you actually needed a specific row (like the top earner).
Interview Questions
What does GROUP BY do?
It divides rows into groups that share the same values in the grouping columns and returns one row per group, with aggregate functions computed separately for each group.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. A query can use both.
Can we use HAVING without GROUP BY?
Yes. The entire result is then treated as a single group, so the query returns one row if the HAVING condition is true, or no rows if it is false.
Why does MySQL give error 1055 with GROUP BY?
The ONLY_FULL_GROUP_BY mode (default since 5.7.5) rejects SELECT columns that are neither grouped, aggregated, nor functionally dependent on the grouping columns, because they have no single value per group.
How are NULLs handled in GROUP BY?
All NULLs in the grouping column are placed into one group, which appears as a NULL row in the result.
How do you find duplicate values in a column?
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;
What is the order of execution of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY?
FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
Is the output of GROUP BY sorted?
Not guaranteed. MySQL 8.0 removed the implicit sort that 5.7 did; use ORDER BY whenever order matters.
What does WITH ROLLUP do?
It adds extra super-aggregate rows: subtotals for each level of the grouping columns and a grand total. GROUPING() returns 1 on those rows.
Difference between GROUP BY and DISTINCT?
Both can return unique values, but GROUP BY is meant for computing aggregates per group; DISTINCT just removes duplicate output rows.
Summary
GROUP BYputs rows into buckets by value and returns one row per bucket.Aggregates (
COUNT,SUM,AVG,MIN,MAX) are computed per group.The SELECT list may contain only grouping columns, aggregates, and things functionally dependent on the grouping columns.
NULLs form a single group; groups with zero rows never appear.
WHEREfilters rows before grouping;HAVINGfilters groups after grouping and can use aggregates.Order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
Always use ORDER BY if you need sorted groups.
MySQL extras:
WITH ROLLUP,GROUPING(),GROUP_CONCAT(),ANY_VALUE(), aliases in GROUP BY and HAVING.
So far our reports show dept_id numbers like 10 and 20 instead of department names, because the names live in another table. In the next chapter, SQL Joins, we learn how to combine rows from several tables.