Summary Data - Grouping
A car showroom needs a database to help track stock. Here is a sample of a proposed design.
Let's do some querying! Note, this is a simple query builder. It doesn't understand the keywords and and or... yet!
| criteria |
|---|
Wouldn't it be great if we could analyse a table to count the number of records that are red cars or sum the total price of all blue and silver cars?! Surely sophisticated database software can handle that!
Yes, it can. And query builders can help us to achieve it!
Skill-up 1 - Grouping
Most popular database applications have a totals button in the query builder menu bar - Σ. It often looks like a sigma (see above).
Download the csv file about car sales.
Import the data into your database application. This data has a date field. Also, the ID field is unique - it can be used as the primary key.
In your query builder, find the totals button. Create a query that displays the Model field twice. The first one should be set to group by, the second one should be set to count.
Your result should look something like:
Save this query as qryCarCount
data file
Be smart! Download the file and save it in a new project folder. Don't leave it in the downloads folder of your computer. You are not a newbie!
Challenge 1 - counting and summing
Design-Test-Redesign the following queries. Save them with the name provided eg qryAverage
Hint! Your queries should produce the same number of records as indicated.
| qryCountSumModels | this query groups by model and shows a count of each model and total sum of price of each model. | 4 records |
| qryColorCount | this query groups by color and shows a count of all white, silver or gold cars priced between 8000 and 10000 inclusive. | gold 3, silver 3, white 4 |
| qryAvgWeekSample | this query groups by date and shows a count of all cars sold between January 11 and January 15 inclusive, along with the average sales price on each date, sorted in ascending order of date. | Jan 11 - 9, 10500; Jan 12 - 9, 9555.55; Jan 13 - 4, 10625; Jan 14 - 1, 12000; Jan 15 - 1, 8500; |
You should now have 4 queries saved in your database application.
Challenge 2 - A chart
Use qryAvgWeekSample to create the following clustered bar chart.
Evidence
In your evidence document, paste a screenshot of each of your 4 saved queries in Design View and a screenshot of your bar chart. Write 3 things that you now know about queries.
Food For Thought
Queries are very smart. They can be used to filter data. They can also be used to group and count and sum and average and... actually, queries can do load of things! Delete records, insert records, create tables, compare records...
Homework
essential vocabulary
database sigma querycriteriaimport evidence table integer screenshot record text ascending field double descending data type Yes/No