In this part, you'll learn how to compute statistics, group rows, and filter such groups. Such operations are extremely important for preparing reports and always come in handy in big tables.

ORDER BY

select *
from employees
order by salary;
copy

By default, ORDER BY ... is ORDER BY ... ASC.

You can also order by DESC.

LIMIT

Limit the number of results.

SELECT *
FROM orders
LIMIT 10;
copy

SELECT salary, position
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
copy

DISTINCT

Gets distinct values.

SELECT DISTINCT customer_id
FROM orders;
copy

You can also use it over a group of columns to get distinct combinations.

SELECT DISTINCT
  customer_id,
  order_date
FROM orders;
copy

COUNT

SELECT COUNT(*)
FROM orders;
copy

Specifying a count for a column will not count null values whereas it will for *.

You can also compose functions.

SELECT COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders;
copy

MIN/MAX/AVG/SUM

These are other functions to help get to greatest/smallest values.

SELECT MIN(total_sum)
FROM orders;

SELECT SUM(salary)
FROM employees
WHERE year = 2014
AND department = 'Marketing';

SELECT department, COUNT(*) as employees_no
FROM employees
WHERE year = 2013
GROUP BY department;

SELECT department, MIN(salary), MAX(salary)
FROM employees
WHERE year = 2014
GROUP BY department;

SELECT department, AVG(salary)
FROM employees
WHERE year = 2015
GROUP BY department;

SELECT last_name, first_name, AVG(salary)
FROM employees
GROUP BY last_name, first_name;
copy

Filter groups

In this section, we'll have a look at how groups can be filtered. There is a special keyword HAVING reserved for this.

SELECT
  customer_id,
  order_date,
  SUM(total_sum)
FROM orders
GROUP BY customer_id, order_date
HAVING SUM(total_sum) > 2000;

SELECT last_name, first_name, COUNT(DISTINCT year) as years
FROM employees
GROUP BY last_name, first_name
HAVING COUNT(DISTINCT year) > 2;

select department, avg(salary)
from employees
where year = 2012
group by department
having avg(salary) > 3000;

select last_name, first_name, sum(salary)
from employees
group by last_name, first_name
order by sum(salary) desc;

select last_name, first_name, avg(salary) as average_salary, count(distinct year) as years_worked
from employees
group by last_name, first_name
having count(distinct year) > 2
order by avg(salary) desc;
copy