Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Flow Control Statements: IF and CASE

Build on your knowledge of SQL statements by learning about controlling their flow.

We'll cover the following...

With our running example of parts for car models, imagine that we are tasked to create a report for the assembly progress of each car model. That is, each car model should have one of three associated states: pending, in progress, or completed. Here, pending means that none of the three parts have been assembled; in progress denotes one or two parts that have been assembled. Finally, completed means that all parts have been successfully assembled.

MySQL
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);

Looking at the data model for CarPart, there is no explicit indication of the assembly state. Rather, the assembly state is given implicitly through a grouping by model, and the number of rows where built_at is not NULL. In SQL, we could achieve this as follows:

MySQL
-- Retrieving the assembly state for all car models based on their number of assembled parts.
SELECT model AS Model, COUNT(built_at) AS `Assembly State`
FROM CarPart
GROUP BY model;

Note that we do not need to verify that built_at is not NULL because COUNT already skips rows where the selected attribute (i.e., built_at) is NULL ...