How to Group by Two Columns in SQL

Problem

You want to group your data by two columns so you can count some statistics.

Example

In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day.

The order table looks like this:

Solution

SELECT
  order_date,
  customer_id,
  SUM(number) AS products_number
FROM order
GROUP BY order_date, customer_id;

The result is:

Discussion

To group by two columns, simply use GROUP BY with two columns. The column names should be listed after the GROUP BY keyword and separated by a comma. Groups will be created based on the values of both columns; for each pair of values, a separate group is created (e.g. ('2023-11-25', 1)). Look at the table below, where each group is presented in a different color:

If one or both columns have NULL values, these values are treated as a separate group (e.g., ('2023-11-26', NULL), (NULL, 5) or (NULL, NULL)).

On the other hand, if there are NULLs in a column on which we apply an aggregate function, the NULL values are simply omitted. (In this example, the aggregate function is SUM() and the column is number). If we had the number values 2, 1, and NULL for one of the groups, the SUM(number) would equal 3 (2 and 1 are added together, and NULL is omitted.)

Similarly, you could group by any number of columns – just write the column names in the GROUP BY clause and separate them with commas.