COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) and the LEFT JOIN COUNT Trap

-- COUNT(*): counts all rows, including those with NULLs
SELECT COUNT(*) AS total_rows FROM transactions;
-- Returns the total number of rows, regardless of NULL values in any column

-- COUNT(column): counts only non-NULL values
SELECT COUNT(txn_description) AS described_txns FROM transactions;
-- Skips rows where txn_description is NULL

-- COUNT(DISTINCT column): unique non-NULL values
SELECT COUNT(DISTINCT account_id) AS unique_accounts FROM transactions;
-- How many different accounts have at least one transaction

-- Demonstration with banking data
CREATE TABLE sample_txns (
  txn_id SERIAL PRIMARY KEY,
  category VARCHAR(20)
);
INSERT INTO sample_txns (category) VALUES ('DEBIT'), ('DEBIT'), ('CREDIT'), (NULL);

SELECT
  COUNT(*) AS count_star,              -- 4
  COUNT(category) AS count_col,         -- 3 (NULL skipped)
  COUNT(DISTINCT category) AS count_dist -- 2 (DEBIT, CREDIT)
FROM sample_txns;

Expected output:
 count_star | count_col | count_dist
------------+-----------+-----------
          4 |         3 |          2

-- The trap: COUNT(*) reports 1 for a childless parent
SELECT a.account_id, a.holder_name, COUNT(*) AS txn_count
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id, a.holder_name
ORDER BY txn_count;

-- The fix: COUNT(child.primary_key) correctly reports 0
SELECT a.account_id, a.holder_name, COUNT(t.txn_id) AS txn_count
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id, a.holder_name
ORDER BY txn_count;

-- See the raw LEFT JOIN output before grouping
SELECT a.account_id, a.holder_name, t.txn_id, t.amount
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE a.account_id = 'FD4001';

package com.bank.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;

public class AccountActivityService {

    // CORRECT: count the child's key column, not COUNT(*)
    public Map<String, Integer> getTransactionCountPerAccount(
            Connection conn) throws SQLException {
        String sql = """
            SELECT a.account_id, a.holder_name, COUNT(t.txn_id) AS txn_count
            FROM accounts a
            LEFT JOIN transactions t ON t.account_id = a.account_id
            GROUP BY a.account_id, a.holder_name
            ORDER BY txn_count DESC
            """;
        Map<String, Integer> result = new LinkedHashMap<>();
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                result.put(rs.getString("holder_name"), rs.getInt("txn_count"));
            }
        }
        return result;
    }
}

-- SUM correctly returns NULL (not 0) for childless parents
SELECT a.account_id, SUM(t.amount) AS total_amount
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id;
-- FD4001: total_amount = NULL (no transactions)
-- Use COALESCE(SUM(t.amount), 0) to convert NULL to 0

-- WRONG: dormant account looks like it has 1 transaction
SELECT a.account_id, COUNT(*) AS txn_count
FROM accounts a LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id;

-- CORRECT: dormant account correctly shows 0
SELECT a.account_id, COUNT(t.txn_id) AS txn_count
FROM accounts a LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id;