Question:
SQLite - Grouping Records and Aggregate functions
1. Grouping Records & Aggregate Functions
Aggregate functions are used to calculate summary information.
Let's assume that every product ordered from the company is supplied in a case of 12 items. An order for 50 units, for instance, means 50 cases of 12 items.
You can create a temporary value that holds the total number of items ordered by entering a statement as shown below. In this example, TotalItems is a temporary value created when the query runs.
SELECT *,
Quantity * 12 AS TotalItems
FROM CustomerData;
1. Try this out so you can see how the results are shown.
Let's assume that A1 costs £0.22 per unit. Now try the following:
2. Create a query that uses a temporary value called OrderValue to hold the total value of each order for A1. Hint: you don't need to calculate TotalItems
Some of the aggregate functions available to you will probably be familiar. These include MAX, MIN, SUM, AVG and COUNT. To count how many records are in the database, for instance, you might enter:
SELECT COUNT(*) FROM CustomerData
3. Try these queries:
a) What is the largest quantity of items ordered?
b) What is the smallest quantity of items ordered?
c) What is the average order size?
d) Can you calculate all of these figures at one time?
e) Can you calculate the biggest/smallest orders for product "C1"?
f) What is the total number of orders for product "A1"?
You can use GROUP BY to group results together. To calculate orders per region, for instance, you might group by postal code area:
SELECT *,
SUM(Quantity)
FROM CustomerData
GROUP BY [Post Code Area];
4. Calculate the total number of orders per product code.