Aggregation Functions

Below, you will find some of the more common aggregate functions that we will focus on in this class. For a more exhaustive list of these functions, you can refer to SQL Aggregate Functions .

Note

The following examples utilize the BooksDB database.

FunctionDefinitionSyntaxValue Returned
SUMReturns sum of all values or DISTINCT values.SELECT SUM(ratings_2) FROM BooksDB.dbo.books;31108850
MAXReturns the highest value.SELECT MAX(ratings_2) FROM BooksDB.dbo.books;436802
MINReturns the lowest value.SELECT MIN(ratings_2) FROM BooksDB.dbo.books;30

NULL Functions

IS NULL

FunctionDefinitionSyntaxValue Returned
IS NULLDetermines whether value is null, often used as a condition with WHERE.See examples below.
Example
SELECT  TOP 5 title, isbn, original_publication_year
FROM BooksDB.dbo.books
WHERE isbn IS NULL AND original_publication_year IS NULL;

Value Returned (3 rows affected)

titleisbnoriginal_publication_year
1BookRags Summary: A Storm of SwordsNULLNULL
2A Shade of Blood (A Shade of Vampire, #2)NULLNULL
3زغازيغNULLNULL
Note

In the first example, we asked for the TOP 5 rows, but only 3 qualified and were returned.

Example
SELECT title, original_title
FROM BooksDB.dbo.books
WHERE authors LIKE 'Sophocles%' AND original_title IS NULL;

Value Returned

titleoriginal_title
1The Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1-3)NULLNULL

IS NOT NULL

FunctionDefinitionSyntax
IS NOT NULLDetermines whether value is not null, often used as a condition with WHERE.See examples below.
Example
SELECT  TOP 5 title, isbn, original_publication_year
FROM BooksDB.dbo.books
WHERE isbn IS NOT NULL AND original_publication_year IS NOT NULL;

Value Returned

(5 rows affected)

titleisbnoriginal_publication_year
1The Hunger Games (The Hunger Games, #1)4390234832008
2Harry Potter and the Sorcerer’s Stone (Harry Potter, #1)4395549341997
3Twilight (Twilight, #1)3160158492005
4To Kill a Mockingbird611200811960
5The Great Gatsby7432735671925
Example
SELECT title, original_title
FROM BooksDB.dbo.books
WHERE authors LIKE 'Sophocles%' AND original_title IS NOT NULL;

Value Returned

titleoriginal_title
1Oedipus Rex (The Theban Plays, #1)Οἰδίπους Τύραννος
2Antigone (The Theban Plays, #3)Ἀντιγόνη

ISNULL

FunctionDefinitionSyntax
ISNULLReplaces a specific null value.See example below.
Note

Note the use of an alias in the example below.

Example
SELECT original_title, ISNULL(original_title, title) AS UpdatedOriginalTitle
FROM BooksDB.dbo.books
WHERE authors LIKE 'Sophocles%';

Value Returned

original_titleUpdatedOriginalTitle
1Οἰδίπους ΤύραννοςΟἰδίπους Τύραννος
2ἈντιγόνηἈντιγόνη
3NULLThe Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1-3)

COALESCE

FunctionDefinitionSyntax
COALESCEReturns the first value that is not null. Can also be used to test multiple expressions unlike ISNULL. Can be used to address null values when paired with string concatenation.See examples below.
Example

The code below demonstrates returning the first non-null value.

SELECT COALESCE(NULL, 'cat', 'bird');
SELECT COALESCE('cat', NULL, 'bird');

Value Returned

catcat
Example

The code below demonstrates using paring COALESCE with concatenation.

SELECT COALESCE(original_title, title) + ' by ' + authors AS 'Reading List'
FROM BooksDB.dbo.books
WHERE authors LIKE 'Sophocles%';

Value Returned

Reading List
1Οἰδίπους Τύραννος by Sophocles, J.E. Thomas
2Ἀντιγόνη by Sophocles, J.E. Thomas
3The Oedipus Cycle: Oedipus Rex/Oedipus at Colonus/Antigone (The Theban Plays, #1–3) by Sophocles, Dudley Fitts, Elena Bono, Robert Fitzgerald

Check Your Understanding

Question

Francis has a database that contains information about car rentals. She wants to create a query that would return the oldest car the company owns. Which aggregation method would be a good fit for this query?

  1. AVG
  2. MIN
  3. COUNT
  4. MAX
Question

Francis wants to find the car with the highest mileage. Which aggregation method would be a good option for this query?

  1. MAX
  2. AVG
  3. COUNT
  4. MIN
Question

Francis wants to create a column that contains a complete address for each car rental office in her database. She has a column for street address, suite number, city, state, and zip code. After some quick EDA, she has discovered that every rental office has a street address, city, state, and zip code. She also discovered that only about 20% have suite numbers while the remaining 80% are null. What function would let her still join these elements into addresses without worrying about the null elements affecting the final output?

  1. COALESCE
  2. IS NOT NULL
  3. ISNULL
  4. IS NULL