How to Use GROUP BY in SQL

GROUP BY allows you to aggregate rows that share common values into summary rows. It’s essential for data analysis in SQL.

Basic Syntax

SELECT column, aggregate_function(column)
FROM table
GROUP BY column;

Simple Example

-- Sample orders table
-- | order_id | customer | product  | amount |
-- | 1        | Alice    | Laptop   | 999    |
-- | 2        | Bob      | Phone    | 699    |
-- | 3        | Alice    | Tablet   | 499    |
-- | 4        | Alice    | Phone    | 699    |
-- | 5        | Bob      | Laptop   | 999    |

-- Count orders per customer
SELECT customer, COUNT(*) as order_count
FROM orders
GROUP BY customer;

-- Result:
-- | customer | order_count |
-- | Alice    | 3           |
-- | Bob      | 2           |

Common Aggregate Functions

COUNT, SUM, AVG

-- Count, sum, and average
SELECT 
    customer,
    COUNT(*) as total_orders,
    SUM(amount) as total_spent,
    AVG(amount) as avg_order
FROM orders
GROUP BY customer;

-- Result:
-- | customer | total_orders | total_spent | avg_order |
-- | Alice    | 3            | 2197        | 732.33    |
-- | Bob      | 2            | 1698        | 849.00    |

MIN and MAX

SELECT 
    customer,
    MIN(amount) as smallest_order,
    MAX(amount) as largest_order
FROM orders
GROUP BY customer;

GROUP BY Multiple Columns

-- Group by customer and product
SELECT 
    customer, 
    product, 
    COUNT(*) as quantity,
    SUM(amount) as total
FROM orders
GROUP BY customer, product;

-- Result shows combinations:
-- | customer | product | quantity | total |
-- | Alice    | Laptop  | 1        | 999   |
-- | Alice    | Phone   | 1        | 699   |
-- | Alice    | Tablet  | 1        | 499   |
-- | Bob      | Laptop  | 1        | 999   |
-- | Bob      | Phone   | 1        | 699   |

Filtering with HAVING

Use HAVING to filter grouped results (WHERE filters before grouping):

-- Customers with more than 2 orders
SELECT customer, COUNT(*) as order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 2;

-- Customers who spent more than $1000
SELECT customer, SUM(amount) as total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 1000;

WHERE vs HAVING

-- WHERE: filters rows BEFORE grouping
SELECT customer, SUM(amount) as total
FROM orders
WHERE amount > 500  -- Only count orders over $500
GROUP BY customer;

-- HAVING: filters groups AFTER aggregation
SELECT customer, SUM(amount) as total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 1000;  -- Only show customers who spent over $1000

-- Combined: use both
SELECT customer, SUM(amount) as total
FROM orders
WHERE amount > 500           -- Filter rows first
GROUP BY customer
HAVING SUM(amount) > 1000;   -- Then filter groups

ORDER BY with GROUP BY

-- Sort by total spent (descending)
SELECT customer, SUM(amount) as total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;

-- Sort by count
SELECT product, COUNT(*) as times_ordered
FROM orders
GROUP BY product
ORDER BY times_ordered DESC;

Practical Examples

Sales Report by Month

SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    COUNT(*) as num_orders,
    SUM(amount) as revenue
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Category Summary

SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

Top Customers

SELECT 
    customer_id,
    customer_name,
    COUNT(order_id) as order_count,
    SUM(total_amount) as lifetime_value
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customer_id, customer_name
HAVING COUNT(order_id) >= 5
ORDER BY lifetime_value DESC
LIMIT 10;

GROUP BY with NULL Values

-- NULLs are grouped together
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department;

-- Result might include:
-- | department | emp_count |
-- | Sales      | 10        |
-- | Marketing  | 5         |
-- | NULL       | 3         |  -- Employees without department

Summary

• GROUP BY groups rows with the same values
• Use aggregate functions: COUNT, SUM, AVG, MIN, MAX
• WHERE filters before grouping
• HAVING filters after grouping
• Can group by multiple columns
• ORDER BY comes after GROUP BY
• NULL values are grouped together