MySQL Crosstab / Pivot Aggregation. Removing counts based on column in other table

Question

THE DATA

I have a set of data in the following format:

CAR_INVENTORY TABLE
CAR_ID  MAKE_MODEL      COLOR   YEAR
1       Ford Fusion     Black   2015
2       Tesla Model S   White   2014
3       Acura ILX       Blue    2013
4       Ford Fusion     Black   2013
5       Toyota Corolla  Blue    2014
6       Ford Fusion     Blue    2013
7       Toyota Corolla  Blue    2012
8       Acura ILX       Black   2015
9       Ford Focus      Blue    2012
10      Ford Fusion     White   2013
11      Acura ILX       Black   2012
12      Toyota Corolla  Black   2015
13      Toyota Corolla  Blue    2014
14      Ford Focus      White   2015
15      Tesla Model S   Red     2015
16      Acura TLX       White   2014
17      Toyota Corolla  Blue    2014
18      Ford Focus      Black   2013

INVENTORY_LOG TABLE
LOG_ID  CAR_ID  NOTE
1       7       Issue with Fuel Guage
2       3       Sweet Ride
3       16      Zippy
4       14      Issue with transmission
5       3       Fun to Drive
6       2       *NULL*
7       8       *NULL*
8       10      Economic
9       15      WOW
10      9       Good Fuel Economy
11      16      Minor issue with Shifting
12      7       Issue with Airbag
13      17      Great Mileage
14      1       Nice Tech
15      13      *NULL*
16      11      Trunk is small
17      12      *NULL*
18      2       Very Speedy
19      7       Good Mileage
20      10      Roomy
21      4       *NULL*
22      6       Nice Tech Package
23      5       Good Economy
24      18      Cool

I know it's not entirely normalized. Let's assume I can't mess with the data.

The car_inventory table has one row for each car in stock. The inventory_log table has at least one entry for each car listed in car_inventory, so each car may have many log entries. The entries in inventory_log can be null.

WHAT I'VE DONE SO FAR

If a car has a log with the word 'issue' in it, it needs to be marked as such. I've figured that part out:

SELECT
    ci.car_id,
    CONCAT(ci.color, " ", ci.make_model) as car,
    SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) AS issue
FROM car_inventory ci
    LEFT JOIN inventory_log il USING (car_id)
GROUP BY ci.car_id
ORDER BY ci.car_id;

This yealds:

car_id  car                     issue
1       Black Ford Fusion       0
2       White Tesla Model S     0
3       Blue Acura ILX          0
4       Black Ford Fusion       0
5       Blue Toyota Corolla     0
6       Blue Ford Fusion        0
7       Blue Toyota Corolla     2
8       Black Acura ILX         0
9       Blue Ford Focus         0
10      White Ford Fusion       0
11      Black Acura ILX         0
12      Black Toyota Corolla    0
13      Blue Toyota Corolla     0
14      White Ford Focus        1
15      Red Tesla Model S       0
16      White Acura TLX         1
17      Blue Toyota Corolla     0
18      Black Ford Focus        0

Which gives a non-zero result for any car with issues.

The next thing I need to do is tally all makes by their color, beyond a certain year. Let's assume we're only interested in Black, White and Blue and we only have Ford's, Acura's, Toyota's and Tesla's (I know I can use a prepared statements to make that dynamic). Got that one in the bag too:

SELECT
    CASE
        WHEN ci.make_model LIKE "Acura%" THEN "Acura"
        WHEN ci.make_model LIKE "Ford%" THEN "Ford"
        WHEN ci.make_model LIKE "Toyota%" THEN "Toyota"
        WHEN ci.make_model LIKE "Tesla%" THEN "Tesla"
    END AS Make,
    SUM(CASE WHEN ci.color = "Black" THEN 1 ELSE 0 END) as Black,
    SUM(CASE WHEN ci.color = "Blue" THEN 1 ELSE 0 END) as Blue,
    SUM(CASE WHEN ci.color = "White" THEN 1 ELSE 0 END) as White
FROM car_inventory ci
    LEFT JOIN inventory_log il USING (car_id)
WHERE 
    ci.year > 2012
GROUP BY Make
ORDER BY Make;

This gives me:

Make    Black   Blue    White
Acura   1       1       1
Ford    3       1       2
Tesla   0       0       1
Toyota  1       3       0

Doing a quick count of the car_inventory table, there are 14 cars newer than 2012, that are black, blue or white.

THE PROBLEM

Here's where I'm having trouble:

What I'm trying to do is combine the two. I need to count all makes by color, where there are no issues.

Here's the result set I'm trying to get:

DESIRED RESULT
MAKE            Black   Blue    White   
Acura           1       1       0
Ford            3       1       1
Tesla           0       0       1
Toyota          1       2       0

With the following three cars removed:

car_id  car                     issues
7       Blue Toyota Corolla     2
14      White Ford Focus        1
16      White Acura TLX         1

I've tried adding AND SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) = 0 to the where clause. This causes mysql error 1111 "Invalid use of group function".

I've also tried HAVING SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) != 0. It yealds incorrect results, only showing the rows for Tesla, and Toyota.

QUESTION

How do I create a crosstab query in MySQL, such that cars (from car_inventory) with log entries (from inventory_log) that have the word 'issue' in them are not counted?

Dave · Accepted Answer

SELECT
    CASE
        WHEN ci.make_model LIKE "Acura%" THEN "Acura"
        WHEN ci.make_model LIKE "Ford%" THEN "Ford"
        WHEN ci.make_model LIKE "Toyota%" THEN "Toyota"
        WHEN ci.make_model LIKE "Tesla%" THEN "Tesla"
    END AS Make,
    SUM(CASE WHEN ci.color = "Black" THEN 1 ELSE 0 END) as Black,
    SUM(CASE WHEN ci.color = "Blue" THEN 1 ELSE 0 END) as Blue,
    SUM(CASE WHEN ci.color = "White" THEN 1 ELSE 0 END) as White
FROM car_inventory ci
WHERE 
    (ci.year > 2012) and
    (ci.car_id not in (select distinct il.car_id from inventory_log il where il.note like '%issue%'))
GROUP BY Make
ORDER BY Make;

MySQL Crosstab / Pivot Aggregation. Removing counts based on column in other table

Answers (2)

Related Questions