import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/dvdrental')
engine.execution_options(isolation_level="AUTOCOMMIT")
%load_ext sql
%sql $engine.url

'Connected: bob@dvdrental'

Aggregate Functions#

Aggregate Functions compute a single result from multiple input rows. For example, let’s say we want to find out the highest temperature:

SELECT max(temperatures_column) FROM weather_table;

There are aggregate functions for computing:

  • max: the maximum of several values.

  • min: the minimum of several values.

  • avg: the average of several values.

  • sum: the sum of several values.

  • count: the count of several values.

These functions are self-explanatory, and have in common that they return a single value out of a group of them. For example, let’s say we want to find out what’s the average amount paid by customers:

%%sql
SELECT avg(amount)
FROM payment;

 * postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
avg
4.2006056453822965

As you can see, we get a lot of decimals, which we can round by using one of the mathematical functions available in PostgreSQL:

%%sql
SELECT round(avg(amount), 2)
FROM payment;

 * postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
round
4.20