website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

GROUP BY

24min

In this chapter, you will find a way to separate rows into groups using the GROUP BY clause.

Overview

The GROUP BY clause returns a group of records from a table or multiple tables with the same values as the specified columns.

The result of the GROUP BY clause returns a single row for each value of the column.

💡 You can use aggregate functions such as COUNT(), MAX(), MIN(), SUM(), etc., to perform the operations on the grouped values in the SELECT query.

Syntax

⚠️ Ensure the column you are using to group is available in the column list.

a) Basic syntax

The basic syntax of the GROUP BY clause is as follows −

Syntax
|
SELECT
column_1, column_2, aggregate_function(column_3)
FROM
table_name
GROUP BY
column_1, column_2,...;


Let’s explore the above syntax:

  • SELECT column_1, column_2, aggregate_function(column_3) defines the columns you want to group (column_1, column_2) and the column that you want to apply an aggregate function to (column_3).
  • FROM table_namedefines the table where the data comes from.
  • GROUP BY column_1, column_2,...; lists the columns that you want to group in the GROUP BY clause.

🧐 The column specified in the SELECT command must also appear in the GROUP BY clause.

b) Syntax with WHERE clause

Please take note that the GROUP BY clause must precisely appear after the WHERE clause, as shown below:

Syntax
|
SELECT
column_1, column_2, aggregate_function(column_3)
FROM
table_name
WHERE
conditions
GROUP BY
column_1, column_2,...;


Examples

Let’s assume that we have two tables here, the customer table and the orders table:

customer table

Create a table
|
CREATE TABLE customer (
  cust_id int,
  cust_name string
);
INSERT INTO customer 
    (cust_id, cust_name) 
VALUES 
    (11001, 'Maya'),
    (11003, 'Ricky'),
    (11009, 'Sean'),
    (11008, 'Chris'),
    (11002, 'Emily'),
    (11005, 'Rue'),
    (11007, 'Tom'),
    (11006, 'Casey');

Display the table
|
SELECT * FROM customer;


It will create a table as shown below:

The customer table
|
+-----------+------------+
| cust_id   | cust_name  |
+-----------+------------+
| 11001     | Maya       |
| 11003     | Ricky      |
| 11009     | Sean       |
| 11008     | Chris      |
| 11002     | Emily      |
| 11005     | Rue        |
| 11007     | Tom        |
| 11006     | Casey      |
+-----------+------------+


orders table

Create a table
|
CREATE TABLE orders (
  order_id int,
  order_date date,
  order_prod string,
  order_qty int,
  order_price int,
  cust_id int
);
INSERT INTO orders 
    (order_id, order_date, order_prod, order_qty, order_price, cust_id) 
VALUES 
    (999191, '2021-01-08','Butter', 1, 4000, 11001),
    (999192, '2021-09-30','Sugar', 1, 10000, 11002),
    (999193, '2021-04-17','Sugar', 1, 10000, 11009),
    (999194, '2021-08-29','Flour', 4, 20000, 11006),
    (999195, '2021-05-04','Sugar', 2, 20000, 11008),
    (999196, '2021-07-27','Butter', 2, 8000, 11006),
    (999197, '2021-10-30','Flour', 2, 10000, 11001),
    (999198, '2021-12-18','Flour', 2, 10000, 11007);

Display the table
|
SELECT * FROM orders;


It will create a table as shown below:

The orders table
|
+------------+--------------+--------------+-------------+---------------+-----------+
| order_id   | order_date   | order_prod   | order_qty   | order_price   | cust_id   |
+------------+--------------+--------------+-------------+---------------+-----------+
| 999191     | 2021-01-08   | Butter       | 1           |  4000         | 11001     |
| 999192     | 2021-09-30   | Sugar        | 1           | 10000         | 11002     |
| 999193     | 2021-04-17   | Sugar        | 1           | 10000         | 11009     |
| 999194     | 2021-08-29   | Flour        | 4           | 20000         | 11006     |
| 999195     | 2021-05-04   | Sugar        | 2           | 20000         | 11008     |
| 999196     | 2021-07-27   | Butter       | 2           | 8000          | 11006     |
| 999197     | 2021-10-30   | Flour        | 2           | 10000         | 11001     |
| 999198     | 2021-12-18   | Flour        | 2           | 10000         | 11007     |
+------------+--------------+--------------+-------------+---------------+-----------+


#Case 1: Basic GROUP BY

Here we will get all product names by grouping them using the products ordered from the orders table:

Group by query
|
SELECT order_prod
FROM orders
GROUP BY order_prod;


The query above will return the output as below:

Output
|
+--------------+
| order_prod   |
+--------------+
| flour        |
| sugar        |
| butter       |
+--------------+


#Case 2: GROUP BY on Multiple Columns

The following example uses multiple columns in the GROUP BY clause:

Group by query
|
SELECT order_id, order_prod
FROM orders
GROUP BY order_id, order_prod;


The above query will create the following result:

Output
|
+-----------+--------------+
| order_id  | order_prod   |
+-----------+--------------+
| 999194    | flour        |
| 999191    | butter       |
| 999196    | flour        |
| 999192    | sugar        |
| 999195    | butter       |
| 999198    | sugar        |
| 999193    | flour        |
| 999197    | sugar        |
+-----------+--------------+


#Case 3: GROUP BY with Aggregate Functions

For this example, we will calculate the total amount each customer has paid for their orders. We will use one of the aggregate functions, i.e., the SUM() function.

Group by query
|
SELECT cust_id, SUM (order_price)
FROM orders
GROUP BY cust_id;


The query above will return the output as shown below:

Output
|
+-----------+----------+
| cust_id   | sum      |
+-----------+----------+
| 11009     | 10000    |
| 11007     | 10000    |
| 11006     | 28000    |
| 11002     | 10000    |
| 11001     | 14000    |
| 11008     | 20000    |
+-----------+----------+


#Case 4: GROUP BY with JOIN Condition

Unlike the previous example, the following query joins the orders table with the customer table and groups customers by their names. Here we will use COUNT() as the aggregate function to count the number of products each customer has purchased.

Group by query
|
SELECT C.cust_name, COUNT (order_prod)
FROM orders O
JOIN customer C ON O.cust_id = C.cust_id
GROUP BY C.cust_name;


The above command will create the following result:

Output
|
+------------+---------+
| cust_name  | count   |
+------------+---------+
| Tom        | 1       |
| Chris      | 1       |
| Casey      | 2       |
| Maya       | 2       |
| Sean       | 1       |
| Emily      | 1       |
+------------+---------+


#Case 5: GROUP BY with Date Data Type

The order_date column uses a DATE data type. In this example, we will group the order’s quantity and total price by dates using the DATE() function.

Group by query
|
SELECT DATE(order_date), order_qty, SUM(order_price)
FROM orders
GROUP BY order_qty, DATE(order_date);


The above query will generate the following result:

Output
|
+---------------+------------+---------+
| date          | order_qty  | sum     |
+---------------+------------+---------+
| 2021-07-27    | 2          | 8000    |
| 2021-08-29    | 4          | 20000   |
| 2021-04-17    | 1          | 10000   |
| 2021-09-30    | 1          | 10000   |
| 2021-05-04    | 2          | 20000   |
| 2021-01-08    | 1          | 4000    |
| 2021-12-18    | 2          | 10000   |
| 2021-10-30    | 2          | 10000   |
+---------------+------------+---------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
WHERE
NEXT
HAVING
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Syntax
a) Basic syntax
b) Syntax with WHERE clause
Examples
#Case 1: Basic GROUP BY
#Case 2: GROUP BY on Multiple Columns
#Case 3: GROUP BY with Aggregate Functions
#Case 4: GROUP BY with JOIN Condition
#Case 5: GROUP BY with Date Data Type

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla