Embedded Files
A reminder of the database we will be using to exemplify the aggregate functions.
What is an Aggregate Function?
What is an Aggregate Function?
Aggregate functions operate on a set of rows to return a single value.
You apply an aggregate function to a set of rows, which may be:
All the rows in the table.
Only those rows specified by a WHERE clause.
Those rows created by a GROUP BY clause (see later).
Aggregate Functions used at Higher
Aggregate Functions used at Higher
AVG() - Returns the average value of a numeric column or expression
COUNT() Returns the number of rows that match the criteria in the WHERE clause
MAX( ) Returns the largest value of the selected column or expression
MIN ( ) Returns the smallest value of the selected column or expression
SUM() Returns the total sum of a numeric column or expression
SUM() and AVG() can only be applied to numeric data types.
MIN() and MAX() works with characters, numeric and date/time data types.
COUNT() works with all data types.
All aggregate functions except COUNT() ignore null values.
COUNT() always returns a positive integer or zero. The other aggregate functions return null if the set contains no rows or contains rows with nulls.
An aggregate function cannot be used in a WHERE clause - so nested queries/views may have to be used.
Is it possible to use more than one aggregate expression in a SELECT statement E.g. SELECT MIN(price), MAX(price)
You cannot mix non-aggregate and aggregate expression in a SELECT statement without using a GROUP BY clause. E.g. SELECT productName, MAX(price)
Computed Fields Example 1 - Average
Computed Fields Example 1 - Average
The sales team want to know the average price of any processors they hold in stock.
Example 1 - Query Design
Example 1 - Query Design
Fields and calculations: AVG(price)
Tables: Item
Search Criteria: category = 'Processors'
Grouping:
Sort Order:
Example 1 - Implementation
Example 1 - Implementation
Computed Fields Example 2- Average and GROUP BY
Computed Fields Example 2- Average and GROUP BY
This time they want to target the higher price items so the company wants to know the average price for each type of item that they stock.
Example 2 - Query Design
Example 2 - Query Design
Fields and calculations: category, stockLevel, AVG(price)
Tables: Item
Search Criteria:
Grouping: group by category, stockLevel
Sort Order:
Explanation
We need to use GROUP BY category as the AVG function will only return a single value but we have more than one category. You need to group by any non aggregate fields.
Example 2 - Implementation
Example 2 - Implementation
Computed Fields Example 3 - SUM
Computed Fields Example 3 - SUM
The company wishes to display the total price of all RAM modules in stock.
Example 3 - Query Design
Example 3 - Query Design
Fields and calculations: SUM(price) AS 'Total Ram Modules'
Tables: Item
Search Criteria: category = "RAM"
Grouping:
Sort Order:
Example 3 - Implementation
Example 3 - Implementation
Computed Fields Example 4 - COUNT
Computed Fields Example 4 - COUNT
The company wants to know how many different models of processors they currently stock.
Example 4 - Query Design
Example 4 - Query Design
Fields and calculations: COUNT(*)
Tables: Item
Search Criteria: category = 'Processors'
Grouping:
Sort Order:
When using the COUNT function you can usually count any field using the * selector
Example 4 - Implementation
Example 4 - Implementation
Computed Fields Example 5 - MAX
Computed Fields Example 5 - MAX
The company wishes to know the highest priced item that they have in stock.
Example 5 - Query Design
Example 5 - Query Design
Fields and calculations: MAX(price)
Tables: Item
Search Criteria: stockQuantity > 0
Grouping:
Sort Order:
Example 5 - Implementation
Example 5 - Implementation
Computed Fields Example 6 - MIN
Computed Fields Example 6 - MIN
The sales team want to know the name and price of the cheapest hard drive.
Example 6 - Query Design
Example 6 - Query Design
Fields and calculations: description, MIN(price)
Tables: Item
Search Criteria: category = 'hard drive'
Grouping:
Sort Order:
Example 6 - Implementation
Example 6 - Implementation
Computed Fields Example 7 - MIN and GROUP BY
Computed Fields Example 7 - MIN and GROUP BY
The company want to target selling the cheapest item in each category.
Example 7 - Query Design
Example 7 - Query Design
Fields and calculations: category, MIN(price)
Tables: Item
Search Criteria:
Grouping: category
Sort Order:
The GROUP BY clause has to be used as there are multiple categories. But the MIN(Price) function will find the smallest item for each category.
Example 7 - Implementation
Example 7 - Implementation
Page updated
Report abuse