COUNT, SUM, AVG, MIN and MAX: what argument types does each accept and return?
answer
- Three families, not five separate rules
- One needs no argument at all
- Two demand numbers, two demand order
- MAX(hire_date) keeps the DATE type
- Result type need not match argument type
basics
~20 sCOUNT accepts any expression, or *, and returns an integer count. SUM and AVG require numeric arguments and return numeric results. MIN and MAX accept any orderable type — numbers, text, dates — and return a value of that same type.
solid answer
~40 sThe five core aggregates split into three families by argument type. **COUNT** is the most permissive: `COUNT(*)` takes no column at all, and `COUNT(expr)` accepts an expression of any type, because it only asks "is this present?". It returns an exact integer. **SUM** and **AVG** are arithmetic, so they demand a numeric argument — `SUM(last_name)` is a type error, not a zero. **MIN** and **MAX** need only an *orderable* type, so they work on numbers, character strings, dates and timestamps, and they return a value of the argument's own declared type: `MAX(hire_date)` is a DATE. The result type is not always the argument type, though — SUM often widens (integer input, bigint or decimal output), and AVG's result type is implementation-defined, which is where integer-average surprises come from.
go deeper
Memorise the three families: COUNT takes anything (or *), SUM and AVG take numbers, MIN and MAX take anything orderable. Be able to say what MAX(hire_date) returns without hesitating.
Explain the result types, not just the argument types — why SUM often widens the integer it was given, and why the standard leaves AVG's precision implementation-defined. Name the DISTINCT quantifier and where it is meaningful.
Show you have been bitten: point out that the two classic production bugs, truncated integer averages and overflowing integer sums, both fall out of result-type rules, and say how you cast to avoid them.
Frame it as a portability contract: the argument types are standard, the result types are partly implementation-defined, so a team shipping SQL across engines should pin the numeric type at the source rather than trusting each engine's widening rules.
## What an aggregate function is An aggregate function collapses a set of input rows into a single value. Applied to a query with no `GROUP BY`, the whole result set is one group and the aggregate produces exactly one row; with a `GROUP BY`, it produces one value per group. Standard SQL defines five core aggregates — `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` — and essentially every aggregation exercise in an interview is composed from them, so their signatures are vocabulary you need cold. ## COUNT: any type in, an integer out `COUNT` is the only aggregate with a special `*` form. `COUNT(*)` takes no argument and counts rows. `COUNT(expr)` takes an expression of **any** type — character, date, numeric, boolean — because it never inspects the value arithmetically, only whether one is present. Both return an exact numeric integer. The width of that integer is engine-specific: PostgreSQL's `count()` returns `bigint`, while SQL Server's `COUNT` returns `int` and offers `COUNT_BIG` when the count can exceed an `int`. That matters mainly when you divide by a count and land in integer arithmetic. ```sql SELECT COUNT(*), COUNT(email), COUNT(hire_date) FROM employees; ``` ## SUM and AVG: numbers only `SUM` and `AVG` are arithmetic, so the standard requires a numeric argument. `SUM(last_name)` is a *type error* — a conforming engine rejects the statement rather than returning zero. (Some engines apply loose implicit conversion instead; that is an engine quirk, not something to rely on.) Several engines also extend the pair to interval types, where summing durations is meaningful. Their result types follow rules worth knowing: - `SUM` of an exact numeric (INTEGER, DECIMAL) is exact numeric; `SUM` of an approximate numeric (REAL, DOUBLE PRECISION) is approximate. Engines usually *widen* the exact case — PostgreSQL's `sum(integer)` returns `bigint` — precisely so that adding many values does not overflow. Engines that do **not** widen can raise an arithmetic overflow. - `AVG` returns an exact numeric for exact input, but the standard leaves the precision and scale implementation-defined. That is exactly why averaging an INTEGER column returns a whole number on some engines and a fractional value on others. ## MIN and MAX: anything you can order `MIN` and `MAX` require only that the argument type has a defined ordering. That covers numeric types, character strings, `DATE`, `TIME`, `TIMESTAMP`, and typically anything else the engine can sort. They return a value of the argument's **own declared type**: `MAX(hire_date)` is a `DATE`, `MIN(city)` is a string of the column's type. What "largest" means is decided by that type's ordering rules — chronological for dates, numeric for numbers, and the column's *collation* for character data. That is why `MAX(version_label)` over the text values `'9'`, `'10'`, `'11'` returns `'9'`: string ordering compares character by character. ```sql SELECT MIN(hire_date), MAX(hire_date), MIN(city), MAX(salary) FROM employees; ``` ## Rules the five share Three properties apply across the family: 1. **Every aggregate returns exactly one value per group** — including when the group is empty of usable values, in which case you get one row, not zero. 2. **All of them except `COUNT(*)` ignore NULL inputs**, since NULL means "unknown" rather than a value to add or compare. 3. **The standard allows a `DISTINCT` quantifier inside `COUNT`, `SUM` and `AVG`** — `SUM(DISTINCT amount)` sums each distinct value once. `MIN(DISTINCT x)` is accepted but pointless, since removing duplicates cannot change an extreme. One restriction also applies to all of them: an aggregate may not take another aggregate as its argument at the same query level. `MAX(COUNT(*))` is rejected; you compute the counts in a subquery and aggregate that. ## Interview framing Interviewers use this as a screening question because a candidate who cannot say "`SUM` needs a number, `MIN`/`MAX` need an order, `COUNT` needs nothing" will fumble every later exercise. Say the three families, then add the sharp edge: the *result* type is not always the argument type, and both of the well-known traps — integer averages truncating and integer sums overflowing — come from those result-type rules rather than from the data.
- Which of the five aggregates accept a DISTINCT quantifier, and which one makes no difference?Standard SQL allows `DISTINCT` inside `COUNT`, `SUM` and `AVG`, where it removes duplicate argument values before aggregating. `MIN(DISTINCT x)` and `MAX(DISTINCT x)` are syntactically accepted but semantically no-ops: dropping duplicates cannot change the smallest or largest value in a set.
- What data type does COUNT return, and when does that width matter?An exact numeric integer, but the width is engine-specific — PostgreSQL returns `bigint`, while SQL Server's `COUNT` returns `int` and provides `COUNT_BIG` for counts beyond an `int`. It matters for very large tables and, more often, when you divide by a count and land in integer arithmetic instead of fractional division.
- Is SUM(last_name) an error or does it just return zero?On a conforming engine it is a type error at parse or bind time: `SUM` is defined over numeric arguments only, so the statement never runs. Some engines apply permissive implicit conversion and quietly coerce the text, which is worse than failing because it can produce a silent wrong answer. Do not rely on it.
saying these in an interview costs you the question
- Claiming SUM works on text and returns zero
- Thinking MIN/MAX only apply to numbers
- Assuming AVG always returns a fractional value
- Saying COUNT can only take * or a numeric column
- Believing the result type always equals the argument type