Data · SQL

How GROUP BY works in SQL

Published

8 orders from 4 customers: how many rows does GROUP BY customer return?

From one group per customer to the column that breaks the query, in five steps:

  1. Sort the rows into groups
  2. Sum up a group with COUNT or SUM
  3. Group by two columns
  4. Spot the column that breaks the query
  5. Practice

How many rows does a GROUP BY return?

With GROUP BY customer, Emma’s 7 orders go into a single group, and that group gives a single row of the result.

Let’s take six orders.

SELECT customer
FROM orders
GROUP BY customer
orders
idcustomercityamount
101EmmaBoston$40
102NoahDenver$25
103EmmaBoston$60
104MiaAustin$15
105NoahBoston$80
106EmmaDenver$35
result
customer
Emma
Noah
Mia
  1. Which orders go into Emma’s group? Tap them in the table.

    Look for Emma in the customer column, from the first row to the last.

  2. Build the other groups the same way, one per different customer. Each group gives one row.

    rows

  1. SELECT customer
    FROM orders
    GROUP BY customer
    orders
    idcustomercityamount
    101NoahBoston$40
    102MiaDenver$25
    103NoahDenver$60
    104LiamBoston$15
    105MiaBoston$80

What do COUNT and SUM return with GROUP BY?

SUM(amount) turns a group into a single number: a group holding $30 and $45 gives one row that shows $75.

Let’s take six other orders, grouped by customer.

SELECT customer, COUNT(*), SUM(amount)
FROM orders
GROUP BY customer
orders
idcustomercityamount
101NoahBoston$15
102EmmaDenver$80
103MiaBoston$35
104EmmaBoston$50
105NoahAustin$20
106EmmaDenver$70
result
customerCOUNT(*)SUM(amount)
Noah2$35
Emma3$200
Mia1$35
  1. COUNT(*) counts the orders that go into Emma’s group.

  2. Add up the amounts of the orders in this group.

    $

  1. SELECT customer, SUM(amount)
    FROM orders
    GROUP BY customer
    orders
    idcustomercityamount
    101MiaBoston$40
    102LiamDenver$25
    103MiaDenver$60
    104LiamBoston$15
    105EmmaBoston$80
    $

How do you GROUP BY two columns?

With GROUP BY customer, city, two rows go into the same group only if they have the same customer and the same city: Emma in Miami and Emma in Seattle make two groups.

Let’s take six orders, shipped to two cities.

SELECT customer, city, COUNT(*)
FROM orders
GROUP BY customer, city
orders
idcustomercityamount
101EmmaBoston$40
102NoahBoston$25
103EmmaDenver$60
104EmmaBoston$15
105NoahBoston$80
106MiaDenver$35
result
customercityCOUNT(*)
EmmaBoston2
NoahBoston2
EmmaDenver1
MiaDenver1
  1. Which orders go into the same group as order 101?

    Look for the rows that have both the customer and the city of order 101.

  2. Build the other groups the same way, one per different customer-city pair.

    rows

  1. SELECT customer, city, COUNT(*)
    FROM orders
    GROUP BY customer, city
    orders
    idcustomercityamount
    101MiaAustin$40
    102LiamAustin$25
    103MiaBoston$60
    104MiaAustin$15
    105LiamBoston$80
    106LiamAustin$35
    107MiaAustin$50
    108NoahBoston$20

Why must a column be in the GROUP BY?

Every column in the SELECT needs one value per group: with GROUP BY customer, if Emma’s group holds $30 and $55, there is no single amount to show for her.

Let’s take this query.

SELECT customer, amount
FROM orders
GROUP BY customer
orders
idcustomercityamount
101EmmaBoston$40
102LiamDenver$25
103EmmaAustin$60
104MiaBoston$15
105LiamDenver$80

ERROR: column "orders.amount" must appear in the GROUP BY clause or be used in an aggregate function

  1. Which amount should Emma’s row show?

    Look at the amounts in Emma’s group.

  2. Two orders then go into the same group only if they have the same customer and the same amount.

    SELECT customer, amount
    FROM orders
    GROUP BY customer, amount
    result
    customeramount
    Emma$40
    Liam$25
    Emma$60
    Mia$15
    Liam$80
    rows

  3. Which fix keeps one row per customer?

  1. SELECT city, customer, SUM(amount)
    FROM orders
    GROUP BY city

    Which column of the SELECT breaks the query?

How do you read a GROUP BY quickly?

Read the GROUP BY line first, since it says what makes a group. Then check that every other column in the SELECT is inside a function.

  1. SELECT city
    FROM orders
    GROUP BY city
    orders
    idcustomercityamount
    101NoahBoston$40
    102EmmaAustin$25
    103MiaBoston$60
    104NoahAustin$15
    105EmmaBoston$80
    106LiamAustin$35