Sentry Answers>SQL>

In SQL how to use GROUP BY on multiple columns?

In SQL how to use GROUP BY on multiple columns?

Richard C.

The ProblemJump To Solution

In SQL, what exactly does GROUP BY do? How does it work when applied to multiple columns instead of just one?

The Solution

Let’s use an example table of people with names and ages. (All commands in this article work on MySQL, PostgreSQL, and MS SQL Server.)

Click to Copy
CREATE TABLE Person ( Id INT PRIMARY KEY, Name VARCHAR(255), Age INT ); INSERT INTO Person (Id, Name, Age) VALUES (1, 'Amir', 25), (2, 'Amir', 25), (3, 'Aya', 2), (4, 'Aya', 30), (5, 'Leila', 8), (6, 'Leila', 35), (7, 'Ndidi', 12), (8, 'Ndidi', 12), (9, 'Ndidi', 25), (10, 'Sofia', 42), (11, 'Mateo', 8), (12, 'Mateo', 67), (13, 'Yara', 35), (14, 'Yara', 35), (15, 'Yara', 4), (16, 'Yara', 35);

First, let’s select Name from the table:

Click to Copy
SELECT Name FROM Person; -- Name -- Amir -- Amir -- Aya -- Aya -- Leila -- Leila -- Ndidi -- Ndidi -- Ndidi -- Sofia -- Mateo -- Mateo -- Yara -- Yara -- Yara -- Yara

Sixteen rows are returned because there are 16 people in the table.

If you want to remove duplicates you can either use DISTINCT or GROUP BY:

Click to Copy
SELECT DISTINCT Name FROM Person; -- Name -- Amir -- Aya -- Leila -- Mateo -- Ndidi -- Sofia -- Yara
Click to Copy
SELECT Name FROM Person GROUP BY Name; -- Name -- Amir -- Aya -- Leila -- Mateo -- Ndidi -- Sofia -- Yara

GROUP BY merges all identical values in a column into a single row, effectively making it return unique values for a column. Unlike DISTINCT, GROUP BY allows you to calculate information related to each group. The functions that do this are called aggregate functions. They are COUNT, SUM, AVG, MIN, and MAX. For example:

Click to Copy
SELECT Name, COUNT(Name) FROM Person GROUP BY Name; -- Name COUNT(Name) -- Amir 2 -- Aya 2 -- Leila 2 -- Ndidi 3 -- Sofia 1 -- Mateo 2 -- Yara 4

If you use GROUP BY on two or more columns, it will merge all pairs of values that are the same in both columns into a single row. In our example, if we group by both Name and Age, then one row will be returned for every person that has the same name and the same age. Here’s the query:

Click to Copy
SELECT Name, Age, COUNT(1) FROM Person GROUP BY Name, Age; -- Name Age COUNT(1) -- Amir 25 2 -- Aya 2 1 -- Aya 30 1 -- Leila 8 1 -- Leila 35 1 -- Mateo 8 1 -- Mateo 67 1 -- Ndidi 12 2 -- Ndidi 25 1 -- Sofia 42 1 -- Yara 4 1 -- Yara 35 3

You can see that there are seven people with the same name, but there are only three people with the same name and age.

Using GROUP BY Name, Age as opposed to GROUP BY Age, Name will make no difference to the results returned (except for the column order).

Do not write GROUP BY Name AND Age instead of GROUP BY Age, Name. Even if your database server accepts this syntax, you will likely get incorrect results.

  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.