Aggregation and Joins

Initial Setup

We will be working with the ClassicModels sample database from MySQL. As for software, we will be using a local installation of MySQL and the MySQL Workbench to interact with it. You can progress once you have installed the above tools and imported the ClassicModels database into the local installation.

Aggregation Queries

COUNT and DISTINCT

Counts the number of rows returned from a query.

-- Count number of rows in a column
SELECT COUNT(customerNumber) FROM payments;

-- Count number of distinct values in a column
SELECT COUNT(DISTINCT customerNumber) FROM payments;

-- Get unique values
SELECT DISTINCT(customerNumber) FROM payments;

GROUP BY and AS

Group By tries to group rows by specified column.

AS creates a column alias.

-- Group rows by one column
SELECT customerNumber, COUNT(*) AS totalPayments FROM payments GROUP BY customerNumber;

-- Create column alias
SELECT customerNumber AS number FROM payments;

Video TBD: 30 min mark