Summarising data

This lesson covers SUM / AVG / COUNT / MIN / MAX, ROUND, GROUP BY, DISTINCT, LIMIT, TO_CHAR. Read it, then solve questions 11–20 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

Aggregate functions

An aggregate turns many rows into one value: COUNT(*) (rows), COUNT(col) (non-NULL values), SUM(col), AVG(col), MIN(col), MAX(col). Without GROUP BY, the whole table is one group, so you get one row.

SELECT COUNT(*) AS bills,
       MIN(sale_date) AS first_sale,
       MAX(quantity_sold) AS biggest_quantity
FROM sales;

Rounding and arithmetic

ROUND(x, 2) keeps two decimals. You can do arithmetic on columns (+ - * /) row by row, and on aggregates. Careful: in PostgreSQL, 7 / 2 between integers is 3; make one side decimal (7 / 2.0) to get 3.5.

SELECT ROUND(AVG(quantity_sold), 2) AS avg_qty,
       7 / 2 AS int_division,
       7 / 2.0 AS decimal_division;

GROUP BY

GROUP BY col splits the rows into one group per value, and every aggregate is computed per group. Rule: every column in SELECT must either be in GROUP BY or be inside an aggregate.

SELECT customer_id,
       COUNT(*) AS bills,
       SUM(quantity_sold) AS units
FROM sales
GROUP BY customer_id
ORDER BY customer_id;

DISTINCT

SELECT DISTINCT removes duplicate rows from the result. With several columns it removes duplicate combinations.

SELECT DISTINCT city
FROM customers;

Keeping only the top rows

LIMIT n keeps the first n rows after sorting, so pair it with ORDER BY. The SQL-standard spelling is FETCH FIRST n ROWS ONLY. (SQL Server uses TOP n.)

SELECT customer_name, joined_on
FROM customers
ORDER BY joined_on
LIMIT 2;

Formatting dates

PostgreSQL’s TO_CHAR(date, 'pattern') turns a date into text. Useful pattern parts: YYYY year, MM month number, Mon short month name, Month full name, DD day, Dy short weekday.

SELECT joined_on,
       TO_CHAR(joined_on, 'DD/MM/YYYY') AS indian_style,
       TO_CHAR(joined_on, 'Dy, DD Mon') AS friendly
FROM customers;