Definition
- Aggregation functions – collapse many rows into a single summary value per group:
COUNT,SUM,AVG,MIN,MAX. - **COUNT(*)** – counts every row in the group, including rows with NULL columns, because it counts row existence.
- COUNT(column) – counts only the rows where that specific column is not NULL.
- COUNT(DISTINCT column) – counts only the distinct non-NULL values in that column.
- NULL skipping – every aggregate except
COUNT(*)silently ignores NULLs, which meansAVGdivides by the count of non-NULL values, not the total row count.
Imagine a clerk at the National Bank of India counting slips in a tray. COUNT(*) is counting every slip in the tray, even blank ones. COUNT(phone_number) is counting only the slips that actually have a phone number written on them, skipping the blanks. SUM(amount) adds up all the money on the slips, AVG(amount) gives the average, and MIN/MAX find the smallest and largest amounts.
Understanding the Concepts
What are aggregation functions?
Aggregation functions take a set of rows and return one value. Without a GROUP BY, they aggregate the entire result set into a single row. With a GROUP BY, they return one row per group. The standard functions are COUNT, SUM, AVG, MIN, and MAX.
-- Sample data at National Bank of India
CREATE TABLE accounts (
account_id SERIAL PRIMARY KEY,
holder_name VARCHAR(100),
balance DECIMAL(15,2),
phone VARCHAR(15),
fee DECIMAL(10,2)
);
INSERT INTO accounts (holder_name, balance, phone, fee) VALUES
('Rajesh Kumar', 150000.00, '9876543210', 500.00),
('Priya Sharma', 85000.00, '9876543210', 300.00),
('Pankaj Gupta', 320000.00, NULL, NULL),
('Amit Verma', 45000.00, '9123456789', 200.00);
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row in the group, including rows that are entirely NULL, because it counts row existence. COUNT(column) counts only the rows where that column is not NULL.
SELECT COUNT(*) AS total_rows, -- 4
COUNT(phone) AS non_null_phones, -- 3
COUNT(DISTINCT phone) AS unique_phones -- 2
FROM accounts;
total_rows | non_null_phones | unique_phones
------------+-----------------+--------------
4 | 3 | 2
Pankaj has no phone number (NULL), so COUNT(phone) returns 3. Rajesh and Priya share the same phone, so COUNT(DISTINCT phone) returns 2.
How does NULL skipping affect SUM and AVG?
Every aggregate except COUNT(*) silently ignores NULLs. This matters most for AVG: it divides the sum of non-NULL values by the count of non-NULL values, not by the total row count. If you want NULLs treated as zero, you must wrap with COALESCE.
-- Fee values: 500, 300, NULL, 200
SELECT SUM(fee) AS total_fees, -- 1000 (NULL skipped)
AVG(fee) AS avg_fee, -- 333.33 (1000 / 3, not 1000 / 4)
MIN(fee) AS min_fee, -- 200
MAX(fee) AS max_fee -- 500
FROM accounts;
total_fees | avg_fee | min_fee | max_fee
------------+---------+---------+--------
1000.00 | 333.33 | 200.00 | 500.00
-- If NULLs should count as zero in the average
SELECT AVG(COALESCE(fee, 0)) AS avg_fee_with_zeros -- 250 (1000 / 4)
FROM accounts;
avg_fee_with_zeros
--------------------
250.00
How do aggregation functions work with GROUP BY?
Without GROUP BY, the aggregate covers the entire table. With GROUP BY, each group gets its own aggregate.
-- Total and average balance per branch
SELECT branch_id,
COUNT(*) AS account_count,
SUM(balance) AS total_balance,
AVG(balance) AS avg_balance
FROM accounts
GROUP BY branch_id
ORDER BY total_balance DESC;
Is COUNT(1) faster than COUNT(*)?
No. COUNT(1) and COUNT(*) behave identically and perform the same. The 1 is a constant, and the optimizer treats both as "count rows." This is a persistent myth.
-- These are identical in behavior and performance
SELECT COUNT(*) FROM accounts; -- counts all rows
SELECT COUNT(1) FROM accounts; -- also counts all rows
What is the COUNT(*) trap with LEFT JOIN?
When you LEFT JOIN and the child table has no match, the child columns are NULL but the row still exists. COUNT(*) counts that row as 1, which is misleading. Use COUNT(child.id) to get the correct count of zero.
-- Pankaj has no transactions (LEFT JOIN produces one row with NULLs)
SELECT a.holder_name,
COUNT(*) AS wrong_count, -- 1 (misleading)
COUNT(t.id) AS correct_count -- 0 (correct)
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE a.holder_name = 'Pankaj Gupta'
GROUP BY a.holder_name;
holder_name | wrong_count | correct_count
--------------+-------------+--------------
Pankaj Gupta | 1 | 0
What happens when SUM has no rows?
SUM over no rows returns NULL, not zero. This catches people off guard when a WHERE clause filters out all rows.
-- No rows match the filter
SELECT SUM(balance) FROM accounts WHERE 1 = 0;
sum
------
NULL
-- Fix with COALESCE
SELECT COALESCE(SUM(balance), 0) FROM accounts WHERE 1 = 0;
coalesce
----------
0.00
How do you use aggregation in Java with JDBC?
Rajesh runs aggregate queries from a Spring service and maps the results using BigDecimal for money.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class AccountSummaryService {
private static final String SUMMARY_SQL =
"SELECT COUNT(*) AS total, " +
" SUM(balance) AS total_balance, " +
" AVG(balance) AS avg_balance " +
"FROM accounts";
public void printSummary(Connection conn) throws Exception {
try (PreparedStatement ps = conn.prepareStatement(SUMMARY_SQL);
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
int total = rs.getInt("total");
BigDecimal totalBalance = rs.getBigDecimal("total_balance");
BigDecimal avgBalance = rs.getBigDecimal("avg_balance");
System.out.println("Total accounts: " + total);
System.out.println("Total balance: " + totalBalance);
System.out.println("Average balance: " + avgBalance);
}
}
}
}
Total accounts: 4
Total balance: 600000.00
Average balance: 150000.00
What should a senior engineer know?
STRING_AGG(PostgreSQL) orGROUP_CONCAT(MySQL) are aggregate functions that concatenate string values within a group.ARRAY_AGG(PostgreSQL) collects values into an array, useful for avoiding multiple queries.- Custom aggregate functions can be created in PostgreSQL with
CREATE AGGREGATE. - In PostgreSQL,
FILTER (WHERE ...)is the modern way to do conditional aggregation:COUNT(*) FILTER (WHERE status = 'ACTIVE'). - For large tables, aggregate queries benefit from covering indexes that include all columns referenced in the GROUP BY and the aggregated expression.
What are common interview follow-up questions?
- What does
AVG(DISTINCT column)do? (Averages only the distinct non-NULL values.) - Can you use aggregation functions in WHERE? (No, use HAVING for aggregate conditions.)
- What is the difference between
SUM(DISTINCT column)andSUM(column)? (SUM(DISTINCT)adds each unique value once, ignoring duplicates.)
Visual Overview
accounts table ┌────────────┬──────────┬─────────┐ │ holder │ phone │ fee │ ├────────────┼──────────┼─────────┤ │ Rajesh │ 98765... │ 500.00 │ │ Priya │ 98765... │ 300.00 │ │ Pankaj │ NULL │ NULL │ │ Amit │ 91234... │ 200.00 │ └────────────┴──────────┴─────────┘ COUNT(*) = 4 (all rows) COUNT(phone) = 3 (non-NULL phones) COUNT(DISTINCT phone) = 2 (unique phones) SUM(fee) = 1000 (NULL skipped) AVG(fee) = 333 (1000 / 3, not 1000 / 4)
Key Takeaway
Aggregation functions collapse many rows into a single summary value per group. The standard ones are COUNT, SUM, AVG, MIN, and MAX. The most important behavior to understand is that every aggregate except COUNT(*) silently ignores NULLs.
The difference between COUNT(*) and COUNT(column) is exactly about NULL handling. COUNT(*) counts every row in the group because it counts row existence, so it includes rows where columns are NULL. COUNT(column) counts only the rows where that specific column is not NULL. At the National Bank of India, if four accounts exist but only three have a recorded phone number, COUNT(*) reports four while COUNT(phone) reports three.
The NULL-skipping behavior matters most for AVG. When Rajesh computed the average transaction fee, the one fee-waived account with a NULL fee was ignored entirely, so the sum of the three real fees was divided by three, not by four. If business rules want NULL counted as zero, you must write AVG(COALESCE(fee, 0)) explicitly.
Remember one sentence: COUNT(*) counts rows, COUNT(column) counts non-NULL values, and SUM and AVG quietly skip NULLs.
---