>>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>>>