- Categories:
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:
Syntax¶
SELECT ...
FROM ...
[ ... ]
GROUP BY groupItem [ , groupItem> [ , ... ] ]
[ ... ]
Where:
groupItem ::= { <column_alias> | <position> | <expr> }
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 | +-------+---------+---------------+