Working with aggregate functions

Applying SQL to Real-World Problems

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

Numeric aggregate functions

SELECT rating,
       AVG(replacement_cost) AS avg_cost,
       COUNT(rating) AS number_elements,
       SUM(replacement_cost) AS total_cost
FROM film
GROUP BY rating;

rating    avg_cost    number_elements    total_cost
PG-13     20.40256    223                4549.77    
R         20.23103    195                3945.05    
G         20.12483    178                3582.22    
PG        18.95907    194                3678.06

String aggregate functions

STRING_AGG(<column>, '<separator>')

SELECT rating,
       STRING_AGG(title, ',') as films
FROM film
GROUP BY rating;

rating       films
PG-13        AIRPLANE SIERRA,ALABAMA DEVIL,...
R            AIRPORT POLLOCK,DATE SPEED,...
G            ACE GOLDFINGER,AFFAIR PREJUDICE,...

Time to aggregate!

Applying SQL to Real-World Problems