Aggregations & Grouping

GROUP BY — the golden rule

Every column in SELECT must either be in GROUP BY, or wrapped in an aggregate function. Nothing else.

-- Transaction count and volume per branch
SELECT   branch,
         COUNT(*)        AS total_txns,
         SUM(amount)     AS total_amount
FROM     transactions
GROUP BY branch
ORDER BY total_amount DESC;

-- Multi-column grouping
SELECT   branch, txn_type,
         COUNT(*) AS txns, SUM(amount) AS volume
FROM     transactions
GROUP BY branch, txn_type
ORDER BY branch, txn_type;

HAVING — filter after grouping

WHERE filters rows before grouping. HAVING filters groups after aggregation.

-- Branches with volume > 1 crore AND > 500 transactions
SELECT   branch,
         COUNT(*)           AS txn_count,
         ROUND(AVG(amount), 2) AS avg_amount
FROM     transactions
WHERE    status = 'SUCCESS'       -- filters rows first
GROUP BY branch
HAVING   COUNT(*) > 500           -- filters groups after
AND      SUM(amount) > 10000000
ORDER BY txn_count DESC;

Aggregate functions

-- Full aggregation report per branch
SELECT
    branch,
    COUNT(*)                   AS total_txns,
    COUNT(DISTINCT account_id)  AS unique_accounts,
    SUM(amount)                AS total_volume,
    ROUND(AVG(amount), 2)      AS avg_txn,
    MAX(amount)                AS largest_txn,
    MIN(txn_date)              AS first_txn_date
FROM   transactions
WHERE  status = 'SUCCESS'
GROUP BY branch
ORDER BY total_volume DESC;

CASE WHEN inside aggregates

This pattern — conditional aggregation — is the most reused pattern in reporting SQL.

-- Pivot status counts into one row per branch
SELECT
    branch,
    COUNT(*)                                        AS total,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) AS success_count,
    COUNT(CASE WHEN status = 'FAILED'  THEN 1 END) AS failed_count,
    SUM(CASE WHEN status = 'SUCCESS' THEN amount ELSE 0 END) AS success_vol,
    ROUND(
        COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) * 100.0 / COUNT(*),
        2
    ) AS success_rate_pct
FROM   transactions
GROUP BY branch
ORDER BY success_rate_pct DESC;

COALESCE & NULL handling

-- COALESCE — return first non-NULL value
COALESCE(segment, 'UNCLASSIFIED')   -- works everywhere
NVL(segment, 'UNCLASSIFIED')       -- Oracle only

-- NULLIF — return NULL if two values are equal (avoids divide-by-zero)
SUM(amount) / NULLIF(COUNT(*), 0)  -- safe division

Date functions — cross-database

-- Monthly transaction summary — Oracle
SELECT
    TO_CHAR(txn_date, 'YYYY-MM') AS txn_month,
    branch,
    COUNT(*)                      AS txn_count,
    SUM(amount)                   AS monthly_volume
FROM   transactions
WHERE  txn_date >= ADD_MONTHS(SYSDATE, -12)
GROUP BY TO_CHAR(txn_date, 'YYYY-MM'), branch
ORDER BY txn_month, branch;