Ga naar inhoud

Header

Aggregating

Introduction

We have learned the following:

  • With GROUP BY you can group.
  • With HAVING you can filter after grouping.
  • With the COUNT() function you can count the number of rows.

For example:

SELECT
  year,
  COUNT(*)
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year count
2005 1
2004 2
2003 4
... ... 
Columns: 2
Rows: 20



  • Here, the COUNT() function is an aggregation function.
  • You count the number of rows.
  • Aggregating is another word for grouping.
  • There are also other aggregation functions.



Commonly used aggregation functions are:

  • COUNT()
    • Counts the number of rows.
  • SUM()
    • Calculates the sum of values in a numeric column.
  • AVG()
    • Calculates the average of values in a numeric column.
  • MIN()
    • Returns the smallest number of values in a numeric column.
  • MAX()
    • Returns the largest number of values in a numeric column.

We will look at these aggregation functions step by step.



1. COUNT(): the number of rows

The example below with COUNT() we have seen before.

  • Grouping:
    • With GROUP BY we group by year.
  • Aggregation functions:
    • With COUNT(*) we count the number of rows.
      • This gives us the number of movies per year.
SELECT
  year,
  COUNT(*) AS movies_count
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year movies_count
2005 1
2004 2
2003 4
... ... 
Columns: 2
Rows: 20



2. SUM(): the number of rows

SUM() calculates the sum of values in a numeric column.

We apply this in the example.

  • Grouping:
    • With GROUP BY we group by year.
  • Aggregation functions:
    • With COUNT(*) we count the number of rows.
    • > With SUM(rank) we sum the scores of movies from a certain year.
SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year movies_count rank_sum
2005 1 (NULL)
2004 2 16.5
2003 4 24.5
... ... ... 
Columns: 3
Rows: 20



3. AVG(): the average

AVG() calculates the average of values in a numeric column.

We apply this in the example.

  • Grouping:
    • With GROUP BY we group by year.
  • Aggregation functions:
    • With COUNT(*) we count the number of rows.
    • With SUM(rank) we sum the scores of movies from a certain year.
    • > With AVG(rank) we calculate the average score per year.
SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year movies_count rank_sum rank_avg
2005 1 (NULL) (NULL)
2004 2 16.5 8.25
2003 4 24.5 8.166666666666666
... ... ... ... 
Columns: 4
Rows: 20



4. MIN(): the smallest number

MIN() returns the smallest number of values in a numeric column.

We apply this in the example.

  • Grouping:
    • With GROUP BY we group by year.
  • Aggregation functions:
    • With COUNT(*) we count the number of rows.
    • With SUM(rank) we sum the scores of movies from a certain year.
    • With AVG(rank) we calculate the average score per year.
    • > With MIN(rank) we get the lowest score per year.
SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg,
  MIN(rank) AS rank_min
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year movies_count rank_sum rank_avg rank_min
2005 1 (NULL) (NULL) (NULL)
2004 2 16.5 8.25 8.2
2003 4 24.5 8.166666666666666 8
... ... ... ... ... 
Columns: 5
Rows: 20



5. MAX(): the largest number

MAX() returns the largest number of values in a numeric column.

We apply this in the example.

  • Grouping:
    • With GROUP BY we group by year.
  • Aggregation functions:
    • With COUNT(*) we count the number of rows.
    • With SUM(rank) we sum the scores of movies from a certain year.
    • With AVG(rank) we calculate the average score per year.
    • With MIN(rank) we get the lowest score per year.
    • > With MAX(rank) we get the highest score per year.
SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg,
  MIN(rank) AS rank_min,
  MAX(rank) AS rank_max
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
year movies_count rank_sum rank_avg rank_min rank_max
2005 1 (NULL) (NULL) (NULL) (NULL)
2004 2 16.5 8.25 8.2 8.3
2003 4 24.5 8.166666666666666 8 8.4
... ... ... ... ... ... 
Columns: 6
Rows: 20



Summary

  • With GROUP BY you can group by one or more columns.
  • With HAVING you can filter (after grouping) by one or more columns.
  • There are multiple aggregation functions:
  • COUNT()
    • Counts the number of rows.
  • SUM()
    • Calculates the sum of values in a numeric column.
  • AVG()
    • Calculates the average of values in a numeric column.
  • MIN()
    • Returns the smallest number of values in a numeric column.
  • MAX()
    • Returns the largest number of values in a numeric column.
  • Example:
SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;
  • There are more aggregation functions than those shown here.
  • Depending on the SQL dialect.
  • For example, for MS SQL Server.