Aggregation and Joins with SQL

The following topics are covered in this tutorial:

Aggregation, grouping and pagination in SQL queries
Mapping functions, arithmetic and working with dates
Combining data from different tables using SQL joins
Improving query performance with indexes
Executing SQL queries using Python and SQLAlchemy

Setting up MySQL Server Locally

We'll use the MySQL server for this tutorial. Make sure to install the following on your computer:

MySQL server: https://dev.mysql.com/downloads/mysql/
MySQL workbench: https://dev.mysql.com/downloads/workbench/

You'll be asked to set a root password while installing MySQL server.

To interact with the MySQL server via the terminal use:

$ /usr/local/mysql/bin/mysql -u root -p

Depending on your operating system the path /usr/local/mysql/bin/mysql may be different. If you're unable to connect, make sure that the server is running and you're using the correct password.

Alternatively, the MySQL Workbench can be used to interact with a MySQL server (local or remote) via a GUI.

Database Setup

In this tutorial, we'll use the Classic Models database. To set up the database locally with sample data:

Download this SQL script: https://raw.githubusercontent.com/JovianHQ/notebooks/refs/heads/main/sql-and-business-intelligence/ClassicModels.sql
In MySQL Workbench, click "File" > "Open SQL Script" to open the script;
Execute the script to create and populate the database.

Once executed, you should be able to view and browse tables in the "Schema" section of the sidebar. If you face an error, make sure you have MySQL server running.

Classic Models Inc. is a distributor of small scale models of cars, motorcycles, planes, ships trains etc. Products manufactured by Classic Models are sold in toy & gift stores around the world. Here's a small sample of their products (source):

Classic Models has offices around the world with dozens of employees. The customers of Classic Models are typically toy/gift stores. Each customer has a designated sales representative (an employee of Classic Models) they interact with. Customers typically place orders requesting several products in different quantities and pay for multiple orders at once via cheques.

Here's the Entity Relationship Diagram (ERD) for the database:

Aggregation, Grouping and Aliases

SQL provides several functions like COUNT, AVERAGE, SUM, MIN and MAX for aggregating the results of a query.

`COUNT`

QUESTION: Report the total number of payments received before October 28, 2004.

We can use the COUNT function to count the number of rows returned for a query.

SELECT COUNT(*) FROM payments WHERE paymentDate<"2004-10-28";

`COUNT` and `DISTINCT`

Another common use case involves counting the number of distinct values in a column.

QUESTION: Report the number of customer who have made payments before October 28, 2004.

We just need to add the DISTINCT keyword before a column name.

SELECT COUNT(DISTINCT customerNumber) FROM payments WHERE paymentDate<"2004-10-28";

`DISTINCT`

Of course, DISTINCT can also be used without COUNT.

QUESTION: Retrieve the list of customer numbers for customer who have made a payment before October 28, 2004.

SELECT DISTINCT customerNumber FROM payments WHERE paymentDate<"2004-10-28";

Learn more about DISTINCT here: https://www.w3schools.com/sql/sql_distinct.asp

Chaining Queries

What if we wanted not just customer numbers, but all the details? We can do this by chaining two SQL queries.

QUESTION: Retrieve the details all customers who have made a payment before October 28, 2004.

We can use the result of the first query (a list of customer numbers) as an input to a second query.

SELECT * FROM customers WHERE customerNumber in 
 (SELECT DISTINCT customerNumber FROM payments WHERE paymentDate<"2004-10-28");

The above question can also be answered using join, which we'll look at later.

EXERCISE: Retrieve details of all the customers in the United States who have made payments between April 1st 2003 and March 31st 2004.

???

`GROUP BY` and `AS`

QUESTION: Find the total number of payments made each customer before October 28, 200.

While performing aggregation, we can specify a column to group rows by. We can also rename aggregate columns using the AS keyword.

SELECT customerNumber, COUNT(*) as totalPayments FROM payments WHERE paymentDate<"2004-10-28" GROUP BY customerNumber;

`SUM`

Apart from the count of rows, we can also compute the sum of values in a column.

QUESTION: Find the total amount paid by each customer payment before October 28, 2004.

SELECT customerNumber, SUM(amount) as totalPayment 
   FROM payments WHERE paymentDate<"2004-10-28" 
   GROUP BY customerNumber;

EXERCISE: Determine the total number of units sold for each product

???

`SUM` and `COUNT`

QUESTION: Find the total no. of payments and total payment amount for each customer for payments made before October 28, 2004.

We can create separate columns for SUM and COUNT

SELECT customerNumber, 
  COUNT(*) as numberOfPayments,  
  SUM(amount) as totalPayment 
  FROM payments 
  WHERE paymentDate<"2004-10-28" 
  GROUP BY customerNumber;

`MIN`, `MAX` and `AVERAGE`

EXERCISE: Modify the above query to also show the minimum, maximum and average payment value for each customer.

???

Sorting and Pagination

Sorting of query results in SQL is done using the ORDER BY keyword. For pagination, you can use the LIMIT and OFFSET keywords.

`ORDER BY` and `LIMIT`

QUESTION: Retrieve the customer number for 10 customers who made the highest total payment in 2004.

SELECT customerNumber, SUM(amount) as totalPayment 
	FROM payments 
    WHERE paymentDate<"2004-10-28" 
    GROUP BY customerNumber 
    ORDER BY totalPayment DESC
    LIMIT 10;

`OFFSET`

To get the next 10 results, we can simply add an OFFSET with the number of rows to skip.

SELECT customerNumber, SUM(amount) as totalPayment 
	FROM payments 
    WHERE paymentDate<"2004-10-28" 
    GROUP BY customerNumber 
    ORDER BY totalPayment DESC
    LIMIT 10 
    OFFSET 10;

Mapping Functions

Apart from aggregation functions, SQL also provides mapping functions like UCASE, LCASE, SUBSTRING, LEN, ROUND, CONCAT that are applied to individual values. Let's look at some examples.

`UCASE` and `CONCAT`

QUESTION: Display the full name of point of contact each customer in the United States in upper case, along with their phone number, sorted by alphabetical order of customer name.

SELECT customerName, 
	CONCAT(UCASE(contactFirstName), " ", UCASE(contactLastName)) AS contact, 
    phone 
    FROM customers 
    WHERE country="USA" 
    ORDER BY customerName;

`SUBSTRING` and `LCASE`

QUESTION: Display a paginated list of customers (sorted by customer name), with a country code column. The country is simply the first 3 letters in the country name, in lower case.

select customerName, 
	LCASE(SUBSTRING(country, 1, 3)) 
    AS countryCode 
    FROM customers ORDER BY customerName;

`ROUND`

QUESTION: Display the list of the 5 most expensive products in the "Motorcycles" product line with their price (MSRP) rounded to dollars.

select productName, 
	ROUND(MSRP) AS salePrice 
    FROM products 
    WHERE productLine="Motorcycles" 
    ORDER BY salePrice DESC 
    LIMIT 5;

Arithmetic Operations

Columns can also be combined using arithmetic operations.

QUESTION: Display the product code, product name, buy price, sale price and profit margin percentage ((MSRP - buyPrice)*100/buyPrice) for the 10 products with the highest profit margin. Round the profit margin to 2 decimals.

SELECT productCode, 
	productName, 
    buyPrice, 
    MSRP, 
    ROUND(((MSRP - buyPrice)*100/buyPrice), 2) AS profitMargin 
    FROM products 
    ORDER BY profitMargin DESC 
    LIMIT 10;

Here is a full list of operators supported in SQL: https://www.w3schools.com/sql/sql_operators.asp

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. There are four types of joins: inner join, left (outer) join, right (outer) join, and (full) outer join.

Here's a visual explanation of different kinds of joins (source):

Inner Join

This is the default join in MySQL.

QUESTION: Show the 10 most recent payments with customer details (name & phone no.).

SELECT checkNumber, paymentDate, amount, customers.customerNumber, customerName, phone 
	FROM payments JOIN customers 
    ON payments.customerNumber=customers.customerNumber 
    ORDER BY paymentDate DESC LIMIT 10;

You can also replace JOIN with INNER JOIN above for clarity.

EXERCISE: Show the full office address and phone number for each employee.

???

EXERCISE: Show the full order information and product details for order no. 10100.

???

Left, Right and Outer Joins

Follow these links to practice left, right and outer joins:

Self Join

A table can also be joined with itself. Each instance of the table can be given a temporary alias.

QUESTION: Show a list of employees with the name & employee number of their manager.

select E.employeeNumber, 
	E.firstName, 
    E.lastName, 
    M.employeeNumber as managerEmployeeNumber, 
    CONCAT(M.firstName, " ", M.lastName) as managerName
    from employees E LEFT JOIN employees M ON E.reportsTo=M.employeeNumber;

Can you explain why we performed a left outer join here? What happens if we perform a inner, right our outer join here?

EXERCISES: Try the following exercises to become familiar with SQL joins:

Report the account representative for each customer.
Report total payments for Atelier graphique.
Report the total payments by date
Report the products that have not been sold.
List the amount paid by each customer.
How many orders have been placed by Herkku Gifts?
Who are the employees in Boston?
Report those payments greater than \$100,000. Sort the report so the customer who made the highest payment appears first.
List the value of 'On Hold' orders.
Report the number of orders 'On Hold' for each customer.
List products sold by order date.
List the order dates in descending order for orders for the 1940 Ford Pickup Truck.
List the names of customers and their corresponding order number where a particular order from that customer has a 13. value greater than $25,000?
Are there any products that appear on all orders?
List the names of products sold at less than 80% of the MSRP.
Reports those products that have been sold with a markup of 100% or more (i.e., the priceEach is at least twice the buyPrice)
List the products ordered on a Monday.
What is the quantity on hand for products listed on 'On Hold' orders?

NOTE: Not all of the above may necessarily require joins, and it may be possible to solve some of the above questions without join.

You can find the solutions for these questions here: https://github.com/harsha547/ClassicModels-Database-Queries

Improving Query Performance

Query performance can be improved using indexes and views.

Index

If you often search or order data by a particular column that's not a primary key, you can add an index to make query performance faster.

QUESTION: Add an index on the lastName column of the customers table.

CREATE INDEX customer_lastname_index ON customers (contactLastName);

This index will speed up queries like:

SELECT * FROM customers WHERE contactLastName="Lee";

and

SELECT * FROM customers ORDER BY contactLastName LIMIT 10;

To view the indexes on a table, run:

SHOW INDEX FROM table_name

Indexes can also be created on multiple columns. Learn more about indexes here: https://www.w3schools.com/sql/sql_create_index.asp

Views

If you perform a query often or frequently join two tables for querying, you can create a virtual table called a "view" to make it easier to write queries.

Here's how a view is created:

CREATE VIEW usaCustomers AS SELECT * FROM customers WHERE country='USA';

A view can be queried just like a table:

select * from usaCustomers WHERE state="CA";

The term usaCustomers is replaced with the query used to create the view.

Learn more about views here: https://www.w3schools.com/sql/sql_view.asp.

Certain relational databases support creation of materialized views which caches the result of query that creates the view. This can significantly speed up query execution.

Executing SQL queries using Python

We can use the SQL Alchemy library to connect to relational databases and execute SQL queries. It also requires a connecting library for the underlying database e.g. MySQL-python for MySQL

Note: To execute the code in this section, you'll need to run this notebook locally on your computer. Press the "Run Locally" button on the Jovian notebook page or lesson page and follow the instructions. Also make sure you have MySQL server running locally with the Classic Models database created and populated.

SQLAlchemy

!pip install sqlalchemy PyMySQL --quiet

from sqlalchemy import create_engine

from getpass import getpass

password = getpass()

········

engine = create_engine('mysql+pymysql://root:{}@localhost:3306/ClassicModels'.format(password))

with engine.connect() as conn:
    result = conn.execute('SELECT officeCode, city, phone FROM offices;')

result

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f88cf2760b8>

offices = list(result)

offices

[('1', 'San Francisco', '+1 650 219 4782'),
 ('2', 'Boston', '+1 215 837 0825'),
 ('3', 'NYC', '+1 212 555 3000'),
 ('4', 'Paris', '+33 14 723 4404'),
 ('5', 'Tokyo', '+81 33 224 5000'),
 ('6', 'Sydney', '+61 2 9264 2451'),
 ('7', 'London', '+44 20 7877 2041')]

import pandas as pd

pd.DataFrame(offices, columns=['officeCode', 'city', 'phoneNumber'])

SQL Alchemy also offers Object Relational Mapping (ORM), a way to map Python classes with database tables, for a more Python-friendly access to a SQL database. Learn about the ORMs here: https://docs.sqlalchemy.org/en/14/orm/tutorial.html#version-check

`%sql` and `%%sql` Jupyter magics

Writing raw SQL queries using SQL Alchemy can be cumbersome. The ipython-sql library provides magic commands to write raw SQL queries in Jupyter notebooks and retrieve results. It uses SQLAlchemy under the hood.

!pip install ipython-sql --quiet

%load_ext sql

from getpass import getpass

password = getpass()

········

conn_str = "mysql+pymysql://root:{}@localhost:3306/ClassicModels".format(password)

%sql {conn_str}

We can now execute queries. Single line queries can be written using %sql and multiline queries using %%sql.

%sql SELECT officeCode, city, phone FROM offices;

 * mysql+pymysql://root:***@localhost:3306/ClassicModels
7 rows affected.

%%sql

SELECT YEAR(paymentDate) as `year`, 
    MONTH(paymentDate) as `month`, 
    ROUND(SUM(amount), 2) as `totalPayments`
    FROM payments 
    GROUP BY `year`, `month` 
    ORDER BY `year`, `month`;

 * mysql+pymysql://root:***@localhost:3306/ClassicModels
30 rows affected.

We can also store results in a variable when using %sql

earnings_result = %sql SELECT YEAR(paymentDate) as `year`, MONTH(paymentDate) as `month`, ROUND(SUM(amount), 2) as `totalPayments` FROM payments GROUP BY `year`, `month` ORDER BY `year`, `month`;

 * mysql+pymysql://root:***@localhost:3306/ClassicModels
30 rows affected.

earnings_df = pd.DataFrame(earnings_result, columns=earnings_result.field_names)

earnings_df.head(10)

!pip install matplotlib seaborn --quiet

import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline
sns.set_style('darkgrid')
matplotlib.rcParams['font.size'] = 14
matplotlib.rcParams['figure.figsize'] = (10, 6)
matplotlib.rcParams['figure.facecolor'] = '#00000000'

plt.title('Earnings by Year and Month ($)')
sns.heatmap(earnings_df.pivot('month', 'year', 'totalPayments'), cmap='Blues');

Summary