Aggregation functions

Use the GROUP BY clause as an identifier, ordinal, or expression.

Ordinal

SELECT gender, sum(age) FROM accounts GROUP BY 1;

Expression

SELECT abs(account_number), sum(age) FROM accounts GROUP BY abs(account_number);

Use aggregations as a select, expression, or an argument of an expression.

Argument

SELECT gender, sum(age) * 2 as sum2 FROM accounts GROUP BY gender;

Expression

SELECT gender, sum(age * 2) as sum2 FROM accounts GROUP BY gender;

• COUNT(field) - Only counts if given a field (or expression) is not null or missing in the input rows.
• COUNT(1) (same as COUNT(*)) - Counts any non-null literal.

Use the HAVING clause to filter out aggregated values.

HAVING with GROUP BY

You can use aggregate expressions or its aliases defined in a SELECT clause in a HAVING condition.

The aggregations in a HAVING clause are not necessarily the same as that in a select list. As an extension to the SQL standard, you’re not restricted to using identifiers only in the GROUP BY list. For example:

Here’s another example for using an alias in a condition.

SELECT gender, sum(age) AS s
FROM accounts
GROUP BY gender
HAVING s > 100;

HAVING without GROUP BY

You can use a HAVING clause without the GROUP BY clause. This is useful because aggregations are not supported in a WHERE clause:

SELECT 'Total of age > 100'
HAVING sum(age) > 100;