AGGREGATE FUNCTIONS

Exercise  - modify an existing query

• Run the Category Sales for 1997 query, change to SQL view, and save as Sales Over 100K

To see only sales totals greater than $100000, add the following criteria after the GROUP BY clause:

            HAVING Sum(ProductSales) > 100000

Run the query and compare the results with the original query - totals should be the same but there should be only two rows.

• Save the original query again as Product Sales First Quarter.  To see only totals for the first quarter, add the following criteria before the GROUP BY clause (ShippedQuarter is a column in the Product Sales for 1997 query):

WHERE ShippedQuarter like "*1"

Run the query and compare the results with the original Category Sales for 1997 - all totals should be substantially lower.

• Change to SQL view and save as First Quarter Sales Over 20K.  To see only totals greater than $20000, add the following criteria after the GROUP BY clause:

HAVING Sum(ProductSales) > 20000

Note: No need to include the table or query name – the ProductSales column only appears in one of the tables or queries.

Run the query and compare the results with the previous query - only 4 values are selected.

Exercises using other aggregate functions

(Note: None of these exercises require more than one table.)

• How many different products are represented in the database?

• How many products are there in each category?

• Find the average freight charged by each shipper.

Modify the original freight query: Average freight by shipper for freight above $75

Modify the original freight query: Average freight by shipper showing only averages above $75

• Find the most popular product (as measured by how many ordered).

• Find the most expensive product.

• Practical example (contrived in this database):

Find duplicate (or more) product listings in Order Details.

Find duplicate (or more) product-price pairings (same product and price) in Order Details

(Compare with Find Duplicates wizard in each case)