Summary Data - Sum, Average, Max, Min
Scenario
Let's continue with the car sales data analysis.
As well as counting, we can SUM data in a field:
SELECT SUM('Price'), Count(*)
FROM carsales
GROUP BY 'Model'
ORDER BY SUM('Price') DESC;
This query sums all of the prices of each model, grouping the data by the model. It orders the result in descending order of SUM('Price').
Task 1
Open up XAMPP and connect to the local host server.
Open the database environment and open the carsales table from the last unit.
Recreate the above SQL. Does it give you the result you expect? Is the result meaningful? Can you make it more meaningful?
Paste a screenshot of your more meaningful query into the worksheet where indicated.
Task 2
Modify the query so that it only displays data relating to Micro cars.
Paste a screenshot of the query into the worksheet where indicated.
Task 3
Modify the previous so that it also has a field showing the average sales price for each model - do research if necessary.
Paste a screenshot of the query into the worksheet where indicated.
Task 4
Modify the previous so that it also has fields showing the maximum sales price and minimum sales price for each model - do research if necessary.
Paste a screenshot of the query into the worksheet where indicated.
Task 5 - Export
Under the query result, select the check all checkbox and click Export.
In the export window, select CSV format and then click .
Task 6 - Import
Import the CSV file into Google Sheets or another spreadsheet application.
Open it in a Google Sheet, or whichever kind of sheet your applicaiton uses.
Task 7 - A Chart
Can you figure out how to create the following chart:
Note: it has a secondary axis on the right side!
Paste a screenshot of your chart into the worksheet where indicated.
SUBMISSION
Submit you worksheet and Google Sheet link as instructed.
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