Grouping data

In the previous step, you learnt about data analytics; now you are going to use SQL to perform some, specifically analysing data of computer sales.

Download and open this version of the computer sales database, which contains the 2019 sales records for a fictitious company that sells Raspberry Pi computers.

To recap, the sales table holds data about each sale made by the company. There is a record for each sale, containing the model_id of the computer sold, the date it was sold, the employee_id of the person who made the sale, and the price it sold for.

Browse the data in the sales table and you will see that there are sales record for every day between 2019-01-01 and 2019-12-31.

Count, sum, and average

The SQL COUNT function can be used in a SELECT statement to return the number of records contained in a query.

Run the following query to return the total number of sales:

SELECT COUNT(*)
FROM sales

This SQL query returns the total number of computer sales in 2019.

Currently, the field returned is named COUNT(*). You can change the field name using the AS statement:

SELECT COUNT(*) AS total_sales
FROM SALES

As with any other SELECT query, a WHERE clause can used to filter the results. By filtering the results between two dates, you can modify the query to show total sales for January 2019.

SELECT COUNT(*) as january_sales
FROM sales
WHERE sale_date >= '2019-01-01'
 AND sale_date <= '2019-01-31'

Two other useful SQL functions for analysing data in databases are SUM and AVG (average).

SUM could be used to find the total value of sales by adding up the price of all sales:

SELECT SUM(price) as total_value
FROM sales

Conversely, AVG can be used to return the average income per sale:

SELECT AVG(price) as average_income
FROM sales

Grouping

COUNT, SUM, and AVG are examples of aggregate functions. Aggregate functions bring data together and provide a summary. In the examples above, the data within the sales data is aggregated to provide a single count, sum, or average.

You can specify how data should be aggregated, or grouped together, by using a GROUP BY statement.

The SQL statement below returns total sales, but includes a GROUP BY employee_id statement.

SELECT COUNT(*) AS total_sales
FROM sales
GROUP BY employee_id

What do you think the four rows that are returned represent?

The four records returned are the total sales made by each employee.

The GROUP BY employee_id statement specified that the COUNT of sales should be aggregated, or split, by the employee_id. As there are four different instances of employee_id in the sales table, four counts were returned.

To create a league table of sales by employee, modify the query to add the employee_id to the SELECT statement and order the results by the total_sales:

SELECT COUNT(*) AS total_sales, employee_id
FROM sales
GROUP BY employee_id
ORDER BY total_sales DESC

How could you use the aggregate functions SUM and GROUP BY to create a league table of the computers that generated the most income from sales? Discuss your thoughts in the comments section.

The SQLite documentation contains information about other available functions that you can use to manipulate data, for example by formatting dates or performing mathematical functions.