SQLConfiguration SQLConfiguration


Select Data  «Prev  Next»

Aggregate Data - Exercise

Creating an aggregate query


Objective: Practice creating an aggregate query.

Exercise scoring

This exercise is worth a total of 30 points. You will receive up to 30 points for accurately creating the aggregate query as described in the instructions.

Instructions

Consider this to be the data in the Sales table to produce your aggregate query:
Division SaleDate SaleAmount
East 01/01/99 100000
East 01/05/99 5000
East 02/02/99 25000
West 01/10/99 60000
North 05/05/99 52000
North 04/13/99 30000
South 01/03/99 25000
South 01/22/99 50000

Use Transact-SQL to create a query containing an aggregate expression that produces a summary resultset that looks exactly like the following:


Division

JanuarySales

East105000
South75000
West60000
Make sure to note the column names returned in the resultset, as you will need to use aliasing. Also note that the JanuarySales column should only contain sales for the month of January.
Hint: To test for a SaleDate column to be in a specific month, use the Month Transact-SQL server function, like this:

Month(datecolumn)

This function returns the numeric month of the date specified by datecolumn. Therefore, you can use this function to test in the WHERE clause that the value returns 1, for January.
Take your time and think this one through!

Submitting the exercise

Type or paste your Transact-SQL statement into the text box below, then click Submit to submit them and view a results page.