Q5 of 26 · SQL
Which aggregate functions do QA engineers use most, and what does each do?
SQLJuniorsqlaggregatescountsumavg
Short answer
Short answer: COUNT, SUM, AVG, MIN, MAX — used to summarise groups of rows. COUNT(*) counts all rows; COUNT(col) skips NULLs. SUM/AVG/MIN/MAX work on numeric or date columns.
Detail
| Function | Returns | Typical QA use |
|---|---|---|
| COUNT(*) | Total rows in group | How many orders in 'failed' state? |
| COUNT(col) | Rows where col is NOT NULL | How many orders have a shipping_date? |
| SUM(col) | Sum of values | Total refund amount processed today |
| AVG(col) | Mean value | Average response time logged in perf table |
| MIN(col) | Smallest value | Earliest created_at in the result set |
| MAX(col) | Largest value | Latest order timestamp |
SELECT
COUNT(*) AS total_orders,
COUNT(shipped_at) AS shipped_orders,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value,
MIN(created_at) AS first_order,
MAX(created_at) AS last_order
FROM orders
WHERE status = 'completed';
A key gotcha: COUNT(col) ignores NULLs. If 5 out of 100 orders have NULL for shipped_at, COUNT(*) = 100 but COUNT(shipped_at) = 95. That discrepancy itself is a useful test assertion.
// EXAMPLE
-- Validate that every completed order has a shipped_at date
SELECT COUNT(*) AS completed, COUNT(shipped_at) AS has_ship_date
FROM orders
WHERE status = 'completed';
-- If completed != has_ship_date, there's a data integrity bug// WHAT INTERVIEWERS LOOK FOR
COUNT(*) vs COUNT(col) distinction. At least one concrete QA example of using an aggregate to assert data correctness.