CPSC340A
Database Systems

Functions Review

• Recall that functions can be used to create new columns by performing computations on existing columns

• Some examples…

  SELECT CONCAT(text1, text2) FROM Table1;
  SELECT ROUND(number, decimals) FROM Table1;

• The function is applied to each row of the table
• Different columns can contribute to the calculation
• However different rows can not contribute to the calculation

• Each row is isolated from other rows

Aggregate Functions

• Aggregate functions use all of the values in a row to compute a new value

• Think average and sum in a spread sheet

  SELECT SUM(number) FROM Table1;

• This retrieves a table with one row

• As usual, the column header can be named

  SELECT SUM(number) AS TOTAL FROM Table1

• Some other aggregate functions

  SELECT AVG(number) FROM Table1;
  SELECT MAX(number) FROM Table1;
  SELECT MIN(number) FROM Table1;
  SELECT COUNT(number) FROM Table1;

• Can have multiple aggregate functions in one query

  SELECT MIN(number), MAX(number) FROM Table1;

• Can not have nested aggregate functions

  SELECT AVG(MAX(number)) FROM Table1;

• But since all the aggregate functions evaluate to a single value this is a little strange and not very useful anyway

• Note that it can’t be combined with a non-aggregate column

  SELECT text, SUM(number) FROM Table;

• Because the retrieved columns are different sizes

• It can be combined with a literal

  SELECT 'Total: ', SUM(number) FROM Table;

• Aggregate functions can be combined with other functions and operators

  SELECT AVG(number * 2) FROM Table1;
  SELECT ROUND(AVG(number)) FROM Table1;

• Can use filters with aggregate functions

  SELECT SUM(number) FROM Table1 where number > 3;

• The filter is applied before aggregate function

• Can join tables with aggregate functions

  SELECT SUM(number) FROM Table1 INNER JOIN Table2 USING (id);

• Again the join is performed before the aggregate function

• To use aggregate function on union, need a subquery

  SELECT SUM(number) FROM (SELECT number1 AS number FROM Table1 UNION SELECT number2 FROM Table2) AS AllNumbers;

• Can’t apply an aggregate function to query

  SUM(SELECT number FROM Table1); -- Error

• Using aggregate functions in a subquery allows the creation of more interesting filters

  SELECT number FROM Table1 WHERE number > (SELECT AVG(number) FROM Table1);

• Note that the aggregate functions include duplicate values
• This may not be desired when counting

• To count unique rows, use DISTINCT in the query

  SELECT DISTINCT COUNT(number) FROM Table1 WHERE number < 4; -- Error

• The distinct should modify the aggregate function

  SELECT COUNT(DISTINCT number) FROM Table1 WHERE number < 4;

• If types are unexpected, it will convert them
• If value is null, it will ignore

• Note that if all rows are null the result will be null

  SELECT SUM(number) FROM Table1 WHERE number IS NULL;

• If you want to include null values in count there is a special syntax

  SELECT COUNT(*) FROM Table1;