Real-world examples of diverse SQL GROUP BY clause usage

Let’s start with the kind of example of GROUP BY you probably saw in your first SQL tutorial, then push it a bit closer to reality.

SELECT
    customer_id,
    SUM(order_total)       AS total_revenue,
    COUNT(*)               AS order_count,
    AVG(order_total)       AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY total_revenue DESC;
``

This is one of the best examples of GROUP BY in everyday analytics work:

- It groups by a business entity (`customer_id`).
- It combines multiple aggregates in one pass.
- It produces metrics you can ship straight to a dashboard.

In production, you’d often join to a `customers` table for readable names:

```sql
SELECT
    c.customer_id,
    c.full_name,
    SUM(o.order_total) AS total_revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY total_revenue DESC;

This is a good warm-up before we look at more diverse examples of SQL GROUP BY clause usage.

Time-based trends: examples of SQL GROUP BY by day, week, and month

Real examples of SQL GROUP BY almost always involve time. Product managers want trends, not just totals.

Daily revenue:

SELECT
    order_date::date          AS order_day,
    SUM(order_total)          AS daily_revenue,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY order_day
ORDER BY order_day;

Weekly revenue using a standard week function (PostgreSQL-style):

SELECT
    date_trunc('week', order_date) AS order_week,
    SUM(order_total)               AS weekly_revenue
FROM orders
GROUP BY date_trunc('week', order_date)
ORDER BY order_week;

Monthly cohort-style summary:

SELECT
    date_trunc('month', order_date) AS order_month,
    COUNT(*)                        AS orders,
    SUM(order_total)                AS revenue,
    COUNT(DISTINCT customer_id)     AS unique_customers
FROM orders
GROUP BY date_trunc('month', order_date)
ORDER BY order_month;

These are classic examples of diverse examples of SQL GROUP BY clause behavior, because the grouping key is not a raw column but an expression (date_trunc). Most modern engines allow expressions directly in GROUP BY, which keeps queries readable.

Conditional aggregates: examples include counts by segment in one pass

You don’t always want separate queries per segment. One of the best examples of modern GROUP BY usage is conditional aggregation with CASE expressions.

SELECT
    DATE(order_date) AS order_day,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_total >= 100 THEN 1 ELSE 0 END) AS high_value_orders,
    SUM(CASE WHEN order_total <  100 THEN 1 ELSE 0 END) AS low_value_orders
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;

Same idea, but segmenting by device type and country in a web analytics table:

SELECT
    country,
    SUM(CASE WHEN device_type = 'mobile'  THEN 1 ELSE 0 END) AS mobile_sessions,
    SUM(CASE WHEN device_type = 'desktop' THEN 1 ELSE 0 END) AS desktop_sessions
FROM web_sessions
GROUP BY country
ORDER BY mobile_sessions DESC;

These real examples of SQL GROUP BY keep your metrics in a single query, which is easier to maintain and faster on large datasets.

Handling NULLs and missing data: realistic examples of SQL GROUP BY clause

Production data is messy. One underrated example of diverse examples of SQL GROUP BY clause usage is normalizing or bucketing NULL and sparse values.

Suppose some customers don’t have a known region:

SELECT
    COALESCE(region, 'Unknown') AS region_label,
    COUNT(*)                    AS customer_count
FROM customers
GROUP BY COALESCE(region, 'Unknown')
ORDER BY customer_count DESC;

Or grouping ages into brackets in a healthcare-style dataset (think of something like CDC or NIH sample data):

SELECT
    CASE
        WHEN age < 18 THEN 'Under 18'
        WHEN age BETWEEN 18 AND 34 THEN '18-34'
        WHEN age BETWEEN 35 AND 49 THEN '35-49'
        WHEN age BETWEEN 50 AND 64 THEN '50-64'
        ELSE '65+'
    END AS age_group,
    COUNT(*) AS patient_count
FROM patient_visits
GROUP BY
    CASE
        WHEN age < 18 THEN 'Under 18'
        WHEN age BETWEEN 18 AND 34 THEN '18-34'
        WHEN age BETWEEN 35 AND 49 THEN '35-49'
        WHEN age BETWEEN 50 AND 64 THEN '50-64'
        ELSE '65+'
    END
ORDER BY patient_count DESC;

Public health datasets from organizations like the CDC or NIH often ship age bands instead of raw ages for privacy, and this pattern matches that structure.

Grouping by multiple dimensions: examples of SQL GROUP BY for pivot-style summaries

Once you start adding more columns to GROUP BY, you’re basically building pivot tables in SQL.

Category by country:

SELECT
    country,
    product_category,
    SUM(order_total) AS revenue,
    COUNT(*)         AS orders
FROM orders
GROUP BY country, product_category
ORDER BY country, revenue DESC;

Add a time dimension for a 3D view:

SELECT
    date_trunc('month', order_date) AS month,
    country,
    product_category,
    SUM(order_total)                AS revenue
FROM orders
GROUP BY date_trunc('month', order_date), country, product_category
ORDER BY month, country, revenue DESC;

These are strong real examples of SQL GROUP BY that map directly to what BI tools generate behind the scenes. When people ask for the “best examples” of GROUP BY for analytics, this multi-dimension pattern is almost always in the answer.

GROUP BY with HAVING: examples of filtering groups by metrics

WHERE filters rows before aggregation. HAVING filters after. Many practical examples of diverse examples of SQL GROUP BY clause usage rely on HAVING to keep only interesting groups.

Customers with at least 5 orders and $500 in revenue:

SELECT
    customer_id,
    COUNT(*)      AS order_count,
    SUM(order_total) AS total_revenue
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5
   AND SUM(order_total) >= 500
ORDER BY total_revenue DESC;

Products with conversion rates above a threshold:

SELECT
    product_id,
    SUM(CASE WHEN event_type = 'view'  THEN 1 ELSE 0 END) AS views,
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases,
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END)::decimal /
    NULLIF(SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END), 0) AS conversion_rate
FROM product_events
GROUP BY product_id
HAVING
    SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) >= 100
    AND
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END)::decimal /
        NULLIF(SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END), 0) > 0.05
ORDER BY conversion_rate DESC;

That second query is a realistic example of SQL GROUP BY for growth and product analytics in 2024–2025, where conversion rate is a standard metric.

GROUP BY with window functions: modern analytics examples

Modern warehouses (Snowflake, BigQuery, Redshift, PostgreSQL, SQL Server, etc.) encourage mixing classic GROUP BY with window functions. These are some of the best examples of SQL GROUP BY clause usage in 2024–2025, because they match what analysts actually need.

Ranking groups by metric

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(order_total) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM customer_revenue
ORDER BY revenue_rank;

Here, GROUP BY creates the base aggregates; the window function ranks those groups without additional joins.

Percent of total within a group

SELECT
    country,
    product_category,
    SUM(order_total) AS revenue,
    SUM(order_total) * 1.0 /
        SUM(SUM(order_total)) OVER (PARTITION BY country) AS pct_of_country_revenue
FROM orders
GROUP BY country, product_category
ORDER BY country, pct_of_country_revenue DESC;

This is a strong example of diverse examples of SQL GROUP BY clause usage: you aggregate by country and category, then use a window over those aggregates to compute percentages.

Real examples of SQL GROUP BY with ROLLUP and CUBE

For finance and operations teams, subtotals and grand totals matter. Many databases support GROUP BY ROLLUP and GROUP BY CUBE for that.

Monthly revenue with a grand total:

SELECT
    date_trunc('month', order_date) AS month,
    SUM(order_total)                AS revenue
FROM orders
GROUP BY ROLLUP (date_trunc('month', order_date))
ORDER BY month;

The final row (where month is NULL) is the grand total. You can label it:

SELECT
    COALESCE(TO_CHAR(date_trunc('month', order_date), 'YYYY-MM'), 'ALL MONTHS') AS month_label,
    SUM(order_total) AS revenue
FROM orders
GROUP BY ROLLUP (date_trunc('month', order_date));

Revenue by country and product with subtotals (syntax varies by engine):

SELECT
    country,
    product_category,
    SUM(order_total) AS revenue
FROM orders
GROUP BY CUBE (country, product_category)
ORDER BY country, product_category;

These are excellent examples of diverse examples of SQL GROUP BY clause usage for executive reporting, because they produce detailed rows and summaries in one query.

Using public and health data: examples include GROUP BY on open datasets

If you want to practice on real data, public datasets from organizations like the U.S. Census Bureau or health-focused sources such as Mayo Clinic and WebMD often end up in teaching databases and analytics sandboxes.

Imagine a simplified hospital_admissions table:

SELECT
    hospital_id,
    diagnosis_code,
    COUNT(*) AS admission_count
FROM hospital_admissions
GROUP BY hospital_id, diagnosis_code
ORDER BY hospital_id, admission_count DESC;

Or a readmission rate example of GROUP BY per hospital:

SELECT
    hospital_id,
    COUNT(*) AS admissions,
    SUM(CASE WHEN readmitted_within_30_days = TRUE THEN 1 ELSE 0 END) AS readmissions,
    SUM(CASE WHEN readmitted_within_30_days = TRUE THEN 1 ELSE 0 END)::decimal /
        NULLIF(COUNT(*), 0) AS readmission_rate
FROM hospital_admissions
GROUP BY hospital_id
ORDER BY readmission_rate DESC;

These real examples of SQL GROUP BY map directly to metrics that health systems and policy analysts care about. They also show how the same pattern you used for e‑commerce revenue can measure outcomes in entirely different domains.

FAQ: examples of SQL GROUP BY clause in everyday work

Q: What are some practical examples of SQL GROUP BY clause usage for beginners?
Start with queries that answer simple business questions: revenue per customer, orders per day, or pageviews per URL. A classic example of this is:

SELECT url, COUNT(*) AS pageviews
FROM pageview_events
GROUP BY url;

From there, add more columns to GROUP BY (like country or device) and experiment with SUM, AVG, and conditional CASE expressions.

Q: Can you give an example of GROUP BY with multiple aggregates and HAVING?
Yes. Counting active users by country with a minimum activity threshold is a good example:

SELECT
    country,
    COUNT(DISTINCT user_id) AS active_users,
    COUNT(*)                AS events
FROM user_events
GROUP BY country
HAVING COUNT(DISTINCT user_id) >= 100
ORDER BY active_users DESC;

Q: When should I use GROUP BY vs window functions?
Use GROUP BY when you need one row per group (customer, day, country). Use window functions when you need per-row detail and group-level metrics side by side. Many of the best examples of SQL GROUP BY in analytics actually combine both: aggregate in a CTE, then rank or compute percentages with windows.

Q: Are these examples of diverse examples of SQL GROUP BY clause portable across databases?
Most patterns here work in PostgreSQL, MySQL, SQL Server, and modern warehouses. Differences show up around date functions (date_trunc vs FORMAT), ROLLUP/CUBE syntax, and type casts. The core idea—group rows to compute aggregates—stays the same.

Q: Where can I find more real examples of SQL GROUP BY based on public data?
Look for sample databases from universities and public institutions. Many tutorials from .edu domains ship with SQL scripts and open datasets you can query yourself. Combining those with the examples of diverse examples of SQL GROUP BY clause patterns in this guide is a solid way to sharpen your skills.