Eric Campbell 5 min read SQLite

SQL: GROUP BY with Multiple Columns

When you need to analyze data across multiple dimensions simultaneously, single-column grouping falls short. Multi-column GROUP BY creates distinct groups based on unique combinations of values...

Key Insights

  • Multi-column GROUP BY creates unique combinations across all specified columns, not separate groupings for each column—grouping by (region, product) is fundamentally different from grouping by region alone
  • Column order in GROUP BY doesn’t affect results in standard SQL, but organizing columns from least to most granular (year, month, day) improves query readability and matches common reporting hierarchies
  • The HAVING clause filters grouped results after aggregation, while WHERE filters rows before grouping—understanding this distinction prevents logic errors and performance issues

Introduction to Multi-Column Grouping

When you need to analyze data across multiple dimensions simultaneously, single-column grouping falls short. Multi-column GROUP BY creates distinct groups based on unique combinations of values across all specified columns, enabling hierarchical and cross-sectional analysis that mirrors real business requirements.

Consider a sales database. Grouping by region tells you total sales per region. Grouping by product category tells you sales per category. But grouping by both region AND product category simultaneously tells you sales for each category within each region—a fundamentally different and more useful dataset.

Here’s the difference in practice:

-- Single-column grouping: total sales per region
SELECT region, SUM(amount) as total_sales
FROM sales
GROUP BY region;

-- Multi-column grouping: sales per product category within each region
SELECT region, product_category, SUM(amount) as total_sales
FROM sales
GROUP BY region, product_category;

The single-column query might return 4 rows (4 regions). The multi-column query could return 20 rows (4 regions × 5 product categories), with each row representing a unique region-category combination.

Basic Syntax and Mechanics

The syntax for multi-column GROUP BY is straightforward: list multiple columns separated by commas after the GROUP BY clause. SQLite evaluates all specified columns together to determine unique groups.

SELECT column1, column2, column3, aggregate_function(column4)
FROM table_name
GROUP BY column1, column2, column3;

The order of columns in the GROUP BY clause doesn’t affect which rows are grouped together in standard SQL. However, it can impact readability and, in some database systems, query optimization.

Here’s a practical example with employee data:

-- Two-column grouping
SELECT department, job_title, COUNT(*) as employee_count
FROM employees
GROUP BY department, job_title;

-- Three-column grouping with multiple aggregates
SELECT department, job_title, employment_type,
       COUNT(*) as headcount,
       AVG(salary) as avg_salary
FROM employees
GROUP BY department, job_title, employment_type;

The first query creates groups for each unique combination of department and job_title. If you have 5 departments and each has 4 job titles, you’ll get up to 20 groups (fewer if some combinations don’t exist in your data).

The second query adds another dimension. Now you’re grouping by department, job title, AND employment type (full-time, part-time, contract), creating even more granular groups.

Practical Use Cases

Multi-column grouping excels at hierarchical reporting and dimensional analysis. Let’s examine scenarios you’ll encounter regularly.

Sales Analysis by Time and Category

-- Quarterly sales performance by product category
SELECT 
    strftime('%Y', sale_date) as year,
    CAST((strftime('%m', sale_date) - 1) / 3 + 1 AS INTEGER) as quarter,
    product_category,
    SUM(amount) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales
WHERE sale_date >= '2023-01-01'
GROUP BY year, quarter, product_category
ORDER BY year, quarter, product_category;

This query creates groups for each year-quarter-category combination, enabling trend analysis across time periods and product lines simultaneously. You can identify which categories perform best in which quarters, spot seasonal patterns, and compare year-over-year growth.

User Engagement Metrics

-- Daily user activity by event type and platform
SELECT 
    DATE(event_timestamp) as event_date,
    event_type,
    platform,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as active_users,
    ROUND(AVG(session_duration_seconds), 2) as avg_duration
FROM user_events
WHERE event_timestamp >= date('now', '-30 days')
GROUP BY event_date, event_type, platform
ORDER BY event_date DESC, event_count DESC;

This groups user activity by date, event type (login, purchase, page_view), and platform (web, iOS, Android), providing a multi-dimensional view of user engagement patterns.

Combining with Aggregate Functions

Aggregate functions become significantly more powerful with multi-column grouping. Each aggregate calculates values for its specific group, not the entire dataset.

Revenue Analysis by Region and Sales Representative

-- Sales performance metrics by region and rep
SELECT 
    region,
    sales_rep_id,
    COUNT(*) as total_sales,
    SUM(amount) as total_revenue,
    ROUND(AVG(amount), 2) as avg_sale_value,
    MAX(amount) as largest_sale,
    MIN(amount) as smallest_sale
FROM sales
WHERE sale_date >= date('now', '-90 days')
GROUP BY region, sales_rep_id
HAVING total_revenue > 10000
ORDER BY region, total_revenue DESC;

This query calculates comprehensive metrics for each sales rep within each region. The HAVING clause filters to show only reps who’ve generated over $10,000 in revenue, demonstrating how to filter on aggregated values.

Customer Segmentation Analysis

-- Average order value by customer segment and payment method
SELECT 
    customer_segment,
    payment_method,
    COUNT(*) as order_count,
    ROUND(AVG(order_total), 2) as avg_order_value,
    SUM(order_total) as total_revenue,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_orders
FROM orders
WHERE order_date >= date('now', '-12 months')
GROUP BY customer_segment, payment_method
HAVING order_count >= 10
ORDER BY customer_segment, avg_order_value DESC;

This reveals purchasing patterns across customer segments and payment methods, helping identify high-value combinations. The window function calculates each group’s percentage of total orders.

Common Patterns and Best Practices

Organize Columns Hierarchically

Structure your GROUP BY columns from broadest to most specific. This matches how humans think about data and makes results easier to interpret:

-- Good: hierarchical organization
SELECT year, month, day, product_category, product_name,
       SUM(quantity) as units_sold
FROM sales
GROUP BY year, month, day, product_category, product_name
ORDER BY year, month, day, product_category;

-- Works but less readable
GROUP BY product_name, day, product_category, month, year

Use HAVING for Post-Aggregation Filtering

WHERE filters before grouping; HAVING filters after. This distinction is critical:

-- Filter individual sales before grouping, then filter groups
SELECT region, product_category, SUM(amount) as revenue
FROM sales
WHERE amount > 100  -- Excludes small individual sales before grouping
GROUP BY region, product_category
HAVING revenue > 50000  -- Keeps only high-revenue combinations
ORDER BY revenue DESC;

Combine Grouped and Aggregate Columns in ORDER BY

-- Sort by grouped column first, then by aggregate
SELECT department, job_title, COUNT(*) as headcount
FROM employees
GROUP BY department, job_title
ORDER BY department, headcount DESC;

This produces results organized by department, with the highest headcount roles listed first within each department.

Troubleshooting Common Mistakes

Mistake 1: Selecting Non-Grouped, Non-Aggregated Columns

-- INVALID: salary isn't grouped or aggregated
SELECT department, job_title, salary, COUNT(*) as headcount
FROM employees
GROUP BY department, job_title;

-- CORRECT: Aggregate the salary or add it to GROUP BY
SELECT department, job_title, AVG(salary) as avg_salary, COUNT(*) as headcount
FROM employees
GROUP BY department, job_title;

-- CORRECT: If you need individual salaries, add to GROUP BY
SELECT department, job_title, salary, COUNT(*) as headcount
FROM employees
GROUP BY department, job_title, salary;

Every column in your SELECT list must either be in the GROUP BY clause or be an aggregate function result.

Mistake 2: Misunderstanding NULL Handling

SQLite treats NULL as a distinct value in GROUP BY. Multiple NULL values group together:

-- NULLs in region will form their own group
SELECT region, COUNT(*) as count
FROM sales
GROUP BY region;

-- Handle NULLs explicitly if needed
SELECT COALESCE(region, 'Unknown') as region, COUNT(*) as count
FROM sales
GROUP BY region;

Mistake 3: Confusing WHERE and HAVING

-- WRONG: Can't use aggregate in WHERE
SELECT region, SUM(amount) as revenue
FROM sales
WHERE revenue > 10000  -- ERROR: revenue doesn't exist yet
GROUP BY region;

-- CORRECT: Use HAVING for aggregate conditions
SELECT region, SUM(amount) as revenue
FROM sales
GROUP BY region
HAVING revenue > 10000;

Multi-column GROUP BY transforms raw data into actionable insights by creating meaningful aggregations across multiple dimensions. Master this technique and you’ll handle complex analytical queries with confidence, whether you’re building dashboards, generating reports, or exploring data patterns. The key is understanding that you’re creating unique combinations—think of it as creating a matrix where each cell represents one group, and your aggregates calculate values for each cell in that matrix.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.