Aggregate functions return a single row based on groups of rows, rather than on single rows. The following table describes the supported aggregate functions.
Table 14: Aggregate functions
Function | Description |
AVG[[ALL | DISTINCT] expression] | Returns the average of the values in a group. Null values are ignored. |
COUNT[{[ALL | DISTINCT] expression] | *}] | Returns the number of items in a group. COUNT[*] returns the number of items in a group, including NULL values and duplicates. COUNT[ALL expression] evaluates expression for each row in a group and returns the number of non-null values. COUNT[DISTINCT expression] evaluates expression for each row in a group and returns the number of unique, non-null values. |
MAX[[ALL | DISTINCT] expression] | Returns the maximum value in the expression. |
MIN[[ALL | DISTINCT] expression] | Returns the minimum value in the expression. |
STDDEV[[ALL | DISTINCT] expression] STDDEV_SAMP[[ALL | DISTINCT] expression] STDEV[[ALL | DISTINCT] expression] | Returns the Sample Standard Deviation of the values in a group. Null values are ignored. |
STDDEVP[[ALL | DISTINCT] expression] STDDEV_POP[[ALL | DISTINCT] expression] STDEVP[[ALL | DISTINCT] expression] | Returns the Population Standard Deviation of the values in a group. Null values are ignored. |
SUM[[ALL | DISTINCT] expression] | Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. |
VAR[[ALL | DISTINCT] expression] VAR_SAMP[[ALL | DISTINCT] expression] | Returns the Sample Variance of the values in a group. Null values are ignored. |
VARP[[ALL | DISTINCT] expression] VAR_POP[[ALL | DISTINCT] expression] | Returns the Population Variance of the values in a group. Null values are ignored. |
Aggregate functions can appear in SELECT lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, OpenAccess SDK divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the SELECT list must be expressions from the GROUP BY clause, expressions containing aggregate functions, or constants. OpenAccess SDK applies the aggregate functions in the SELECT list to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, OpenAccess SDK applies aggregate functions in the SELECT list to all the rows in the queried table or view.
Many aggregate functions accept these options:
• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
• ALL causes an aggregate function to consider all values, including all duplicates.
For example:
SELECT max[sal], MIN[sal], AVG[sal] FROM emp;
SELECT deptno, MAX[sal], SUM[sal] FROM emp GROUP BY deptno;
SELECT deptno, COUNT[empno] FROM emp GROUP BY deptno;
SELECT deptno, VAR_SAMP[sal], STDDEV_SAMP[sal] FROM emp GROUP BY deptno;
SELECT deptno, VAR_POP[sal], STDDEV_POP[sal] FROM emp GROUP BY deptno;
Note: Aggregate functions in denominator are not supported.
Note: Return value of statistical functions on a single value set is 0 [zero].
SQL Tip December 27, 2013March 9, 2019 3 Minutes I’ve been asked about counting NULL values several times so I’m going to blog about it in hopes others
will be helped by this explanation of NULL values in SQL and how to COUNT them when necessary. Note, my examples make use of a table found in the System Center Configuration Manager database. First, it’s important to know what a NULL is in SQL. A NULL in SQL simply means no value exists for the field. Comparisons for NULL cannot be done with an “=” or “!=” [or “”] operators*. Additionally, NULL ‘values’ will not be JOINed when in a JOIN [meaning
a NULL value in one table.column does not “=” a NULL value in the other table.column]. This means rather than saying something like “WHERE NullableField = NULL” you must instead say “WHERE NullableField IS NULL” when trying to find NULLs [or NOT find NULLs]**. We’re ready now to look at the solutions: The COUNT function can tell you the total number of rows returned in a result set [both NULL and non-NULL together depending on how it’s used].
For example: · Using SELECT COUNT[*] or SELECT COUNT[1] [which is what I prefer to use] will return the total of all records returned in the result set regardless of NULL values. · Using
COUNT[]will count the number of non-NULL items in the specified column [NULL fields will be ignored]. Thus, you could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields, for example: But, that would be boring. So, instead we’ll do it a more non-boring way. We’ll be using the CASE statement to determine which rows have NULLs and create a way for SQL to COUNT [or SUM] them.
Here’s what that looks like [I could’ve used a COUNT instead of a SUM]: The reason this will work is because when there is no “ELSE” in a CASE statement any row not meeting the criteria is treated as a NULL. Since the COUNT [and other aggregate functions] will ignore NULL values we use the CASE to turn NULLs into values and values into NULLs. Here’s a real-life example of what this looks like [using a modified version of the query sent to
me]:
*Technically it is possible if you tell SQL to not think like SQL, but I don’t count that as a solution or even an option. If you are using “SET ANSI_NULLS OFF” in your scripts I suggest you re-write them. Here’s what Books Online says about this option: In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development
work, and plan to modify applications that currently use this feature. **To see this in a real example, try running the following to see the behavior:SELECT COUNT[1] - COUNT[]
SUM[CASE WHEN IS NULL THEN 1 END]
SELECT LastStatusMessageIDName
,COUNT[1] AS [Count of Total Records]
,COUNT[LastExecutionResult] AS [Count of Non-NULL Records]
,SUM[CASE WHEN LastExecutionResult IS NULL THEN 1 END] AS [Count of NULL Records]
FROM dbo.v_ClientAdvertisementStatus
WHERE AdvertisementID = 'CAZ201AE'
AND LastStateName != 'Succeeded'
GROUP BY LastStatusMessageIDName
ORDER BY 4 DESC;
/***** Comparing a NULL field *****/
SELECT COUNT[1] FROM dbo.v_ClientAdvertisementStatus WHERE LastExecutionResult IS NULL AND AdvertisementID = 'CAZ201AE' -- This will count the total records that have a NULL “LastExecutionResult” [for the advertisement]
SELECT COUNT[1] FROM dbo.v_ClientAdvertisementStatus WHERE LastExecutionResult = NULL AND AdvertisementID = 'CAZ201AE' -- This doesn’t work because no values technically “equal” “NULL”!
Published December 27, 2013March 9, 2019