Categories:: Query Syntax

GROUP BY¶

Groups rows with the same group-by-item expressions and computes aggregate functions for the resulting group. A GROUP BY expression can be a column name, a number referencing a position in the SELECT list, or a general expression.

Extensions:: GROUP BY CUBE , GROUP BY GROUPING SETS , GROUP BY ROLLUP

Syntax¶

SELECT ...
FROM ...
[ ... ]
GROUP BY groupItem [ , groupItem> [ , ... ] ]
[ ... ]

Where:

groupItem ::= { <column_alias> | <position> | <expr> }

column_alias: Column alias appearing in the query block’s SELECT list.
position: Position of an expression in the SELECT list.
expr: Any expression on tables in the current scope.

Examples¶

This example shows the gross revenue per product, grouped by product_id:

-- Create the sales table and insert some rows.
CREATE TABLE sales (product_ID INTEGER, retail_price REAL,
                    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES
     (1, 2.00,  1, 'SF', 'CA'),
     (1, 2.00,  2, 'SJ', 'CA');
     (2, 5.00,  4, 'SF', 'CA');
     (2, 5.00,  8, 'SJ', 'CA');
     (2, 5.00, 16, 'Miami', 'FL');
     (2, 5.00, 32, 'Orlando', 'FL');
     (2, 5.00, 64, 'SJ', 'PR');

-- Show the gross revenue, grouped by product_id.
-- In other words, show the total amount of money received for
-- selling each product.
SELECT product_ID, SUM(retail_price * quantity) AS gross_revenue
  FROM sales
  GROUP BY product_ID;

Output:

+------------+---------------+
| PRODUCT_ID | GROSS_REVENUE |
+============+===============+
|          1 |          6    |
+------------+---------------+
|          2 |        620    |
+------------+---------------+

This example builds on the previous example, showing the net profit per product, grouped by product_id:

-- Create the products table and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES (1, 1.00);
INSERT INTO products (product_ID, wholesale_price) VALUES (2, 2.00);

-- Show the net profit on each product.
SELECT p.product_ID, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit
  FROM products AS p, sales AS s
  WHERE s.product_ID = p.product_ID
  GROUP BY p.product_ID;

Output:

+------------+--------+
| PRODUCT_ID | PROFIT |
+============+========+
|          1 |      3 |
+------------+--------+
|          2 |    372 |
+------------+--------+

You can group by multiple columns:

SELECT State, City, SUM(retail_price * quantity) AS gross_revenue
 FROM sales
 GROUP BY State, City;

Output:

+-------+---------+---------------+
| STATE |   CITY  | GROSS REVENUE |
+=======+=========+===============+
|   CA  | SF      |            22 |
+-------+---------+---------------+
|   CA  | SJ      |            44 |
+-------+---------+---------------+
|   FL  | Miami   |            80 |
+-------+---------+---------------+
|   FL  | Orlando |           160 |
+-------+---------+---------------+
|   PR  | SJ      |           320 |
+-------+---------+---------------+