Back to cookbooks list Articles Cookbook

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:

order_dateproduct_idcustomer_idnumber
2023-11-25711
2023-11-251213
2023-11-265312
2023-11-26124
2023-11-26321
2023-11-261627
2023-11-26332
2023-11-27631
Download SQL for Data Analysis Cheat Sheet

Solution:

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

The result is:

order_datecustomer_idproducts_number
2023-11-2632
2023-11-2731
2023-11-26212
2023-11-2514
2023-11-2612

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:

order_datecustomer_idproduct_idnumber
2023-11-25171
2023-11-251123
2023-11-261532
2023-11-26214
2023-11-26231
2023-11-262167
2023-11-26332
2023-11-27361

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.

Recommended courses:

Recommended articles:

See also: