Summarizing Data with Aggregate Function

A query can produce generalization about groups of values as well as individual column values. It does this through the use of aggregate function. Aggregate function produce a single value for entire group of columns. Here is a list of these function:

• COUNT – Produce the number of rows or non –NULL column values that the query select
• SUM – Produce the arithmetic sum of all selected values of given column.
• AVG – Produce the average (mean) of all selected value of a given column.
• MAX – Produce the largest of all selected values of given column.
• MIN – Produce the smallest of all selected of given column.

How Do You Use Aggregate Function?

Aggregate functions are used like column names in the SELECT clause of queries and, with one exception, take column name as argument. Only numeric column can be used for SUM and AVG. for COUNT, MAX and MIN, any datatype can be used. When used with character column, MAX and MIN will be interpreted in term of collection which is to say that, generally speaking, MIN will means first and MAX last , in alphabetical order. NULL value are ignore in all calculations.

To find Sum of all of all of our purchases from order table, we could enter the following query, those output is show in figure.

SELECTSUM(AMT)FROM #tempOrder

Special Attribute of COUNT

The COUNT function is slightly different. It counts the number of values in a given column or number of rows in a table. When it is counting column values, you can combine it with DISTINCT to produce account of the different values in a given column.

Counting Values – we could use count, for example, to count the visitor currently listed in #VISITOR table show in figure.

CREATETABLE #VISITOR(ID INTIDENTITY(1,1),VISITORID INT,DATEOFVISIT DATETIME)

INSERTINTO #VISITOR

SELECT 1,GETDATE()UNIONALLSELECT 2,GETDATE()UNIONALLSELECT 3,GETDATE()UNIONALLSELECT 2,GETDATE()UNIONALLSELECT 1,GETDATE()
UNIONALLSELECT 4,GETDATE()UNIONALLSELECT 5,GETDATE()UNIONALLSELECT 2,GETDATE()UNIONALLSELECT 2,GETDATE()


SELECTCOUNT(VISITORID) [WITHOUT DISTINCT] FROM #VISITOR

SELECTCOUNT(DISTINCT VISITORID ) [WITH DISTINCT] FROM #VISITOR

If we had not specified DISTINCT, we would have gotten a count of all rows that had duplicate or non-NULL values.

AVG: AVG function is used to produce the average of selected value

SELECTAVG(AMOUNT) AVGAMOUNT FROM dbo.CustomerOrders

MAX: This function produce the largest of all selected values of given column

SELECTMAX(AMOUNT) AVGAMOUNT FROM dbo.CustomerOrders

Don’t Nest Aggregates

In a strict International ISO SQL, you cannot take an aggregate of an aggregate. Consider the following query.

SELECT VISITORID,max(sum(VISITORID)) [WITHOUT DISTINCT] FROM #VISITOR groupby VISITORID

Your statement will probably be rejected.