Mod 6: SQL Aggregate Functions

d00004136; Adrienne Watt; Watt, Adrienne

19 Mod 6: SQL Aggregate Functions

The SQL data manipulation language (DML) is used to query and modify database data. In this chapter, we will describe how to use the SELECT with GROUP BY.

SELECT Statement

The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria. It is processed according to the following sequence:

SELECT DISTINCT item(s)
FROM table(s)
WHERE predicate
GROUP BY field(s)
ORDER BY fields

SELECT statement with GROUP BY clause

The GROUP BY clause is used to create one output row per each group and produces summary values for the selected columns, as shown below.

SELECT type
FROM Books
GROUP BY type

Here is an example using the above statement.

SELECT type AS ‘Type’, MIN(price) AS ‘Minimum Price’
FROM Books
WHERE royalty > 10
GROUP BY type

If the SELECT statement includes a WHERE criterion where price is not null,

SELECT type, price
FROM Books
WHERE price is not null

then a statement with the GROUP BY clause would look like this:

SELECT type AS ‘Type’, MIN(price) AS ‘Minimum Price’
FROM Books
WHERE price is not null
GROUP BY type

Using COUNT with GROUP BY

We can use COUNT to tally how many items are in a container. However, if we want to count different items into separate groups, such as marbles of varying colours, then we would use the COUNT function with the GROUP BY command.

The below SELECT statement illustrates how to count groups of data using the COUNT function with the GROUP BY clause.

SELECT COUNT(*)
FROM Books
GROUP BY type

Using AVG and SUM with GROUP BY

We can use the AVG function to give us the average of any group, and SUM to give the total.

Example #1 uses the AVG FUNCTION with the GROUP BY type.

SELECT AVG(qty)
FROM Books
GROUP BY type

Example #2 uses the SUM function with the GROUP BY type.

SELECT SUM(qty)
FROM Books
GROUP BY type

Example #3 uses both the AVG and SUM functions with the GROUP BY type in the SELECT statement.

SELECT ‘Total Sales’ = SUM(qty), ‘Average Sales’ = AVG(qty), stor_id
FROM Sales
GROUP BY StorID ORDER BY ‘Total Sales’

Restricting rows with HAVING

The HAVING clause can be used to restrict rows. It is similar to the WHERE condition except HAVING can include the aggregate function; the WHERE cannot do this.

The HAVING clause behaves like the WHERE clause, but is applicable to groups. In this example, we use the HAVING clause to exclude the groups with the province ‘BC’.

SELECT au_fname AS ‘Author”s First Name’, province as ‘Province’
FROM Authors
GROUP BY au_fname, province
HAVING province <> ‘BC’

Built-in Functions

There are many built-in functions in SQL Server such as:

Aggregate: returns summary values
Conversion: transforms one data type to another
Date: displays information about dates and times
Mathematical: performs operations on numeric data
String: performs operations on character strings, binary data or expressions
System: returns a special piece of information from the database
Text and image: performs operations on text and image data

Below you will find detailed descriptions and examples for the first four functions.

Aggregate functions

Aggregate functions perform a calculation on a set of values and return a single, or summary, value. Table 16.4 lists these functions.

FUNCTION	DESCRIPTION
AVG	Returns the average of all the values, or only the DISTINCT values, in the expression.
COUNT	Returns the number of non-null values in the expression. When DISTINCT is specified, COUNT finds the number of unique non-null values.
COUNT(*)	Returns the number of rows. COUNT(*) takes no parameters and cannot be used with DISTINCT.
MAX	Returns the maximum value in the expression. MAX can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MAX finds the highest value in the collating sequence. MAX ignores any null values.
MIN	Returns the minimum value in the expression. MIN can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MIN finds the value that is lowest in the sort sequence. MIN ignores any null values.
SUM	Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only.

Table 16.4 A list of aggregate functions and descriptions.

Below are examples of each of the aggregate functions listed in Table 16.4.

Example #1: AVG

SELECT AVG (price) AS ‘Average Title Price’
FROM Books

Example #2: COUNT

SELECT COUNT(PubID) AS ‘Number of Publishers’
FROM Publishers

Example #3: COUNT

SELECT COUNT(province) AS ‘Number of Publishers’
FROM Publishers

Example #3: COUNT (*)

SELECT COUNT(*)
FROM Employees
WHERE job_lvl = 35

Example #4: MAX

SELECT MAX (HireDate)
FROM Employees

Example #5: MIN

SELECT MIN (price)
FROM Books

Example #6: SUM

SELECT SUM(discount) AS ‘Total Discounts’
FROM Discounts

Conversion function

The conversion function transforms one data type to another.

In the example below, a price that contains two 9s is converted into five characters. The syntax for this statement is SELECT ‘The date is ‘ + CONVERT(varchar(12), getdate()).

SELECT CONVERT(int, 10.6496)
SELECT title_id, price
FROM Books
WHERE CONVERT(char(5), price) LIKE ‘%99%’

In this second example, the conversion function changes data to a data type with a different size.

SELECT title_id, CONVERT(char(4), ytd_sales) as ‘Sales’
FROM Books
WHERE type LIKE ‘%cook’

Date function

The date function produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts. If the date parameter is a smalldatetime value, the result is also a smalldatetime value.

The DATEADD function is used to add and increment date values. The syntax for this function is DATEADD(datepart, number, date).

SELECT DATEADD(day, 3, hire_date)
FROM Employees

In this example, the function DATEDIFF(datepart, date1, date2) is used.

This command returns the number of datepart “boundaries” crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all data types such as minutes, seconds, and milliseconds.

SELECT DATEDIFF(day, HireDate, ‘Nov 30 1995’)
FROM Employees

For any particular date, we can examine any part of that date from the year to the millisecond.

The date parts (DATEPART) and abbreviations recognized by SQL Server, and the acceptable values are listed in Table 16.5.

DATE PART	ABBREVIATION	VALUES
Year	yy	1753-9999
Quarter	qq	1-4
Month	mm	1-12
Day of year	dy	1-366
Day	dd	1-31
Week	wk	1-53
Weekday	dw	1-7 (Sun.-Sat.)
Hour	hh	0-23
Minute	mi	0-59
Second	ss	0-59
Millisecond	ms	0-999

Table 16.5. Date part abbreviations and values.

Mathematical functions

Mathematical functions perform operations on numeric data. The following example lists the current price for each book sold by the publisher and what they would be if all prices increased by 10%.

SELECT Price, (price * 1.1) AS ‘New Price’, title
FROM Books
SELECT ‘Square Root’ = SQRT(81)
SELECT ‘Rounded‘ = ROUND(4567.9876,2)
SELECT FLOOR (123.45)

Terms

aggregate function: returns summary values

ASC: ascending order

conversion function: transforms one data type to another

date function: displays information about dates and times

DESC: descending order

GROUP BY: used to create one output row per each group and produces summary values for the selected columns

mathematical function: performs operations on numeric data

SELECT statement: used to query data in the database

string function: performs operations on character strings, binary data or expressions

system function: returns a special piece of information from the database

text and image functions: performs operations on text and image data

wildcard: allows the user to match fields that contain certain letters.

License

Icon for the Creative Commons Attribution 4.0 International License