Purchase Solution

SQLite - Grouping Records and Aggregate functions

Not what you're looking for?

Ask Custom Question

Please see attached file. I have to use software SQLite, database attached along with questions.

9.2 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.

Purchase this Solution

Solution Summary

The grouping records and aggregate functions for SQLites are examined in the solution.

Solution Preview

See the attached file.

9.2 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 ...

Purchase this Solution


Free BrainMass Quizzes
Java loops

This quiz checks your knowledge of for and while loops in Java. For and while loops are essential building blocks for all Java programs. Having a solid understanding of these constructs is critical for success in programming Java.

Basic Networking Questions

This quiz consists of some basic networking questions.

C++ Operators

This quiz tests a student's knowledge about C++ operators.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

Word 2010: Table of Contents

Ever wondered where a Table of Contents in a Word document comes from? Maybe you need a refresher on the topic? This quiz will remind you of the keywords and options used when working with a T.O.C. in Word 2010.