SQL Server

>>Previous>>

Aggregate Functions

Aggregate functions can return summary values for an entire table or for group of rows in a table. Aggregate functions are usually used in conjunction with the GROUP BY clause and are used in HAVING Clause or in the column_list.

· AVG: Returns average of the values in the numeric expression

Example

SELECT AVG(ytd_sales)
FROM Titles

· COUNT(*): Returns number of selected rows

Example

SELECT Count(*) from employee [ Returns total number of rows in a table]

· MAX: Returns highest value in the expression

Example

SELECT MAX(ytd_sales) FROM titles

· MIN: Returns lowest value in the expression

Example

SELECT MIN (ytd_sales)
FROM titles

· SUM: Returns total of values in the numeric expression

Example

SELECT SUM(qty)
FROM sales

· TOP n: Returns the top n values in the result set

SELECT TOP 5 * FROM authors

SELECT TOP 5 * FROM sales where qty>20

Group By and Having Clause

The group by clause summary data that meets the WHERE clause criteria to be returned as single row. The HAVING clause set the criteria to determine which rows will be returned by the GROUP BY clause

Example

SELECT title_id, count (title_id) as Number_of_Authors
From Titleauthor
GROUP BY title_id Having count (title_id)>1

OR

SELECT title_id,ytd_sales
FROM Titles
WHERE (ytd_sales>=4000)
GROUP BY title_id,ytd_sales

1. HAVING clause has same effect on the GROUP BY clause as the WHERE clause has on the SELECT statement.

2. GROUP BY clause must contain all nonaggregate columns from the SELECT column_list.

3. HAVING clause criteria columns must return only one value.

>>>Next>>>