SQL Count Group Your Guide to Smart Data Analysis

SQL: Counting and Grouping Examples

Below are examples demonstrating how to use COUNT with GROUP BY in SQL for common analytics tasks.

1. Count rows per category

• Use GROUP BY on the category column.
• Use COUNT(*) to count rows in each group.

1. Count distinct values per group

• Use COUNT(DISTINCT column) inside each group.

1. Filter groups by aggregated count

• Use HAVING (not WHERE) to filter based on COUNT.

1. Combine grouping with ordering

• Use ORDER BY on the aggregate (e.g., COUNT(*) DESC).

-- 1. Count number of orders per customer
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

-- 2. Count distinct products ordered per customer
SELECT
    customer_id,
    COUNT(DISTINCT product_id) AS distinct_products
FROM orders
GROUP BY customer_id
ORDER BY distinct_products DESC;

-- 3. Customers with more than 10 orders (filtering groups)
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
ORDER BY order_count DESC;

-- 4. Count orders per day
SELECT
    order_date,
    COUNT(*) AS orders_per_day
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- 5. Count orders per status, including only statuses with at least 100 orders
SELECT
    status,
    COUNT(*) AS status_count
FROM orders
GROUP BY status
HAVING COUNT(*) >= 100
ORDER BY status_count DESC;

SQL COUNT() with GROUP BY: From Raw Data to Insight

Combining COUNT() with GROUP BY lets you turn huge, noisy tables into compact summaries that answer real business questions like:

• How many orders has each customer placed?
• How many employees work in each department?
• How many articles exist in each content category?

Instead of a single total row count, you get counts per category, which is the foundation of almost all analytical reporting.

Core Syntax

```sql