Lesson 3 of 6
Query the Data with SQL
You write SQL queries against your SQLite database to filter, group, and calculate total revenue per item.
8 minData Analysis
After this lesson, you will be able to write SQL queries that filter rows, group them by item, and calculate total revenue for each item in your sales database.
Start the SQLite shell
You already have a database file called sales.db from the earlier lessons. Open a terminal in the folder that contains sales.db and start the SQLite shell by typing:
sqlite3 sales.dbThe prompt changes to sqlite>. You can type SQL commands here and end each one with a semicolon. To leave the shell, type .quit and press Enter.
Look at the data
First, see all the rows in the sales table:
SELECT * FROM sales;You will see the rows you loaded from sales.csv. The columns are date, item, quantity, and unit_price.
Filter rows with WHERE
A WHERE clause keeps only the rows that meet a condition. For example, to see only rows for apples:
SELECT * FROM sales WHERE item = 'apple';The condition is item = 'apple'. Text values go inside single quotes. You can also filter on numbers. To see rows where the quantity is more than 2:
SELECT * FROM sales WHERE quantity > 2;You can combine conditions with AND or OR. To see rows for apples with quantity more than 2:
SELECT * FROM sales WHERE item = 'apple' AND quantity > 2;Group rows with GROUP BY
To calculate something for each item, group the rows by item. The GROUP BY clause puts rows with the same item into one group. Then you can use an aggregate function like SUM to add up values in each group.
For example, to get the total quantity sold for each item:
SELECT item, SUM(quantity) AS total_quantity
FROM sales
GROUP BY item;The AS total_quantity gives a name to the calculated column. The result shows one row per item with the sum of its quantities.
Calculate total revenue per item
Revenue for one sale is quantity * unit_price. To get the total revenue for each item, multiply inside the SUM function:
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item;This query groups all rows by item, then for each group it multiplies quantity by unit price for every row and adds those products together. The result is a list of items and their total revenue.
Order the results
To see the items with the highest revenue first, add an ORDER BY clause:
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item
ORDER BY total_revenue DESC;DESC means descending order. Use ASC for ascending order. The default is ascending, so you can omit ASC.
Filter groups with HAVING
A WHERE clause filters rows before grouping. If you want to filter groups after aggregation, use HAVING. For example, to show only items with total revenue greater than 5:
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY item
HAVING total_revenue > 5;You can use both WHERE and HAVING in the same query. WHERE filters rows first, then grouping happens, then HAVING filters groups.
Put it together
Here is a complete query that filters to sales after a certain date, groups by item, calculates total revenue, and orders the result:
SELECT item, SUM(quantity * unit_price) AS total_revenue
FROM sales
WHERE date >= '2024-01-16'
GROUP BY item
HAVING total_revenue > 2
ORDER BY total_revenue DESC;This query keeps only rows with date on or after 2024-01-16, groups them by item, calculates total revenue for each group, keeps only groups with revenue over 2, and sorts the result from highest to lowest revenue.
Exit the shell
When you are done, type .quit and press Enter to return to your normal terminal prompt.
Key takeaways
- Use
WHEREto filter rows before grouping. - Use
GROUP BYto combine rows with the same item. - Use
SUM(quantity * unit_price)to calculate total revenue per item. - Use
HAVINGto filter groups after aggregation. - Use
ORDER BYto sort the result.
Try it
Open the SQLite shell on sales.db. Write a query that shows the total revenue for each item, but only for rows where the quantity is 2 or more. Order the results so the item with the highest total revenue appears first. Run the query and check that the revenue values match what you would get by hand for those rows. A good result is a query that returns one row per item with a column named total_revenue, sorted from highest to lowest, and includes only items that have at least one sale with quantity 2 or more.
Official documentation
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.