SQL GROUP BY: Aggregate Rows into Groups

GROUP BY combines rows that have the same grouping value so aggregate functions can calculate one result for each group. For example, count the students in each class:

SELECT class,
       COUNT(*) AS total_records
FROM student
GROUP BY class
ORDER BY class;

Instead of one total for the complete table, this produces one row for each class.

SQL GROUP BY Syntax Top ↑

SELECT group_column,
       aggregate_function(column_name)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition
ORDER BY group_column;

WHERE, HAVING and ORDER BY are optional. Their roles are different: WHERE filters source rows, GROUP BY forms groups, HAVING filters grouped results, and ORDER BY controls result order.

GROUP BY with COUNT() Top ↑

Count the number of students in each class:

SELECT class,
       COUNT(*) AS total_records
FROM student
GROUP BY class;

COUNT(*) counts the rows inside each class group.

GROUP BY with WHERE Top ↑

Use WHERE when only selected source rows should take part in the grouping.

Count female students in each class:

SELECT class,
       COUNT(*) AS total_records
FROM student
WHERE gender = 'female'
GROUP BY class
ORDER BY class;

The filtering happens first. GROUP BY then creates groups from the remaining rows.

GROUP BY with COUNT(), AVG(), MAX(), MIN() and SUM() Top ↑

Several aggregate functions can be calculated for every group in one query:

SELECT class,
       COUNT(*) AS total_students,
       AVG(mark) AS average_mark,
       MAX(mark) AS highest_mark,
       MIN(mark) AS lowest_mark,
       SUM(mark) AS total_mark
FROM student
GROUP BY class
ORDER BY total_students DESC,
         class ASC;

Related aggregate tutorials: SUM(), AVG(), MAX() and MIN().

GROUP BY Multiple Columns Top ↑

More than one column can define the group. This query creates a separate group for every unique class-and-gender combination:

SELECT class,
       gender,
       COUNT(*) AS total_students
FROM student
GROUP BY class, gender
ORDER BY class, gender;

The dedicated multiple-column GROUP BY tutorial continues this topic with more examples.

Conditional Aggregation with GROUP BY and CASE Top ↑

Instead of returning separate rows for male and female students, conditional aggregation can return both counts as columns in each class row:

SELECT class,
       COUNT(*) AS total_students,
       SUM(
           CASE WHEN gender = 'male'
                THEN 1
                ELSE 0
           END
       ) AS male_students,
       SUM(
           CASE WHEN gender = 'female'
                THEN 1
                ELSE 0
           END
       ) AS female_students
FROM student
GROUP BY class
ORDER BY class;

See SQL CASE for conditional expressions.

WHERE vs HAVING with GROUP BY Top ↑

WHERE filters rows before they are grouped. HAVING filters the grouped aggregate results.

Return only classes containing more than five students:

SELECT class,
       COUNT(*) AS total_students
FROM student
GROUP BY class
HAVING COUNT(*) > 5
ORDER BY total_students DESC;

Use both when necessary:

SELECT class,
       COUNT(*) AS female_students
FROM student
WHERE gender = 'female'
GROUP BY class
HAVING COUNT(*) >= 3;

Find Duplicate Values with GROUP BY Top ↑

A common practical use is finding values that appear more than once.

SELECT name,
       COUNT(*) AS duplicate_count
FROM student2_1
GROUP BY name
HAVING COUNT(*) > 1;

GROUP BY identifies the duplicate values; removing duplicate rows is a separate operation and requires a clear rule for which record should be kept.

Delete duplicate records after identifying them

GROUP BY and NULL Values Top ↑

If the grouping column contains NULL values, the NULL rows form one group for that grouping expression.

SELECT class,
       COUNT(*) AS total_students
FROM student3
GROUP BY class;

To display a label instead of NULL:

SELECT IFNULL(class, 'Not Known') AS class_name,
       COUNT(*) AS total_students
FROM student3
GROUP BY class;

See SQL NULL values.

Selected Columns and ONLY_FULL_GROUP_BY Top ↑

A grouped query should not select an arbitrary non-aggregated value that is unrelated to the grouping.

This is problematic:

-- Avoid an arbitrary non-grouped column
SELECT class,
       name,
       COUNT(*) AS total_students
FROM student
GROUP BY class;

There can be several different names inside one class group, so which name should SQL return?

With MySQL's ONLY_FULL_GROUP_BY SQL mode, invalid grouped selections are rejected unless the non-aggregated expression is functionally dependent on the grouping columns.

For beginner and portable queries, a safe rule is:

• select the columns that define the group, and
• apply aggregate functions to the other values you need.

For example:

SELECT class,
       COUNT(*) AS total_students,
       AVG(mark) AS average_mark
FROM student
GROUP BY class;

ORDER BY Grouped Results Top ↑

ORDER BY can sort by the grouping column or an aggregate alias.

SELECT class,
       COUNT(*) AS total_students
FROM student
GROUP BY class
ORDER BY total_students DESC,
         class ASC;

Do not rely on GROUP BY itself to provide the desired display order. Use ORDER BY whenever order matters.

GROUP BY with PHP PDO Top ↑

A grouped query that contains no external values can be executed directly with PDO query().

<?php
require 'config.php';

$sql="SELECT class,
            COUNT(*) AS total_records
      FROM student
      GROUP BY class
      ORDER BY class";

$stmt=$dbo->query($sql);

echo '<table class="table table-striped">';

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $class=htmlspecialchars(
        (string)$row['class'],
        ENT_QUOTES,
        'Windows-1252'
    );

    $total=(int)$row['total_records'];

    echo "<tr><td>$class</td><td>$total</td></tr>";
}

echo '</table>';

If the grouped query includes values from a form or URL, use a prepared statement for those values. See fetching MySQL records with PDO.

GROUP BY Date Values Top ↑

Date and datetime columns can be grouped by derived periods such as year or month.

SELECT YEAR(sale_date) AS sale_year,
       MONTH(sale_date) AS sale_month,
       COUNT(*) AS total_sales
FROM sales
GROUP BY YEAR(sale_date),
         MONTH(sale_date)
ORDER BY sale_year, sale_month;

Group date records by calendar year, financial year and month

GROUP BY Performance Notes Top ↑

• GROUP BY can require sorting, temporary work or index scans depending on the query plan.
• Indexes on grouping/filter columns can help some queries, but usefulness depends on WHERE conditions and the selected aggregates.
• Filter unnecessary source rows with WHERE before grouping when the business requirement allows it.
• Avoid transferring detailed rows to application code merely to recreate a grouping SQL can calculate directly.
• Use EXPLAIN when a production GROUP BY query is unexpectedly slow.

Common GROUP BY Mistakes Top ↑

Thinking GROUP BY automatically means COUNT() Top ↑

GROUP BY defines groups. COUNT() is only one aggregate that can be calculated for those groups.

Using WHERE for an aggregate condition Top ↑

Use WHERE to filter source rows and HAVING to filter aggregate results such as COUNT(*) > 5.

Selecting arbitrary non-grouped columns Top ↑

A group can contain several different values for an ungrouped column. Select grouping columns and aggregates unless a valid functional dependency applies.

Assuming GROUP BY sorts the output Top ↑

Use ORDER BY when result order matters.

Forgetting that NULL forms a group Top ↑

Rows with NULL in the grouping expression are grouped together for that expression.

Deleting duplicates based only on a grouped result Top ↑

GROUP BY can identify duplicate values, but deleting duplicates requires an explicit rule for which physical row to retain.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: