SQL（三）.pptVIP

sql(3) Aggregate Functions Aggregate Functions Aggregate functions are used to compute against a returned column of numeric data from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, GROUP BY. Although they are required for the GROUP BY clause, these functions can be used without the GROUP BY clause. Aggregate Functions -----example: SELECT AVG(salary)FROM employee; This statement will return a single result which contains the average value of everything returned in the salary column from the employee table. Aggregate Functions -----example: SELECT AVG(salary)FROM employee;WHERE title = Programmer; This statement will return the average salary for all employees whose title is equal to Programmer Aggregate Functions -----Example: SELECT Count(*)FROM employees; This particular statement is slightly different from the other aggregate functions since there isnt a column supplied to the count function. This statement will return the number of rows in the employees table. Aggregate Functions---Review Exercises Select the maximum price of any item ordered in the items_ordered table. Hint: Select the maximum price only. Select the average price of all of the items ordered that were purchased in the month of Dec. Aggregate Functions---Review Exercises What are the total number of rows in the items_ordered table? For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Hint: Your query should return the price only. Aggregate Function Exercise Answers Exercise #1 SELECT max(price)FROM items_ordered; Exercise #2 SELECT avg(price)FROM items_orderedWHERE order_date LIKE %Dec%; Exercise #3 SELECT count(*) FROM items_ordered; Exercise #4 SELECT min(price) FROM items_ordered WHERE item = Tent; * * returns the number of rows in a table COUNT(*) returns the total number of values in