[SOLVED] How do I count and display unique column combinations when using a case statement

How do I count and display unique column combinations when using a case statement

I want to get a count of unique column combinations and display the count by the concatenated string of the column values. I use a case statement on each column to transform the column values into fewer items. It ends up displaying the proper concatenation string, but the count is based on the values before they were changed with the CASE statements. In this example I want to count the car brand purchased by state. Any errors in SQL are from my poor typing ability. The SQL with the actual values works. I have tried using a temp table, but could not get it to work.

Expected Result - The count represents the concatenated values from the CASE statements

Toyota in Utah      5
Ford in California  6

Actual Results - The concationated CASE values are displayed, but the rows are the count of the values before the case transform.

Purchase             Total
Toyota in Utah       2    
Toyota in Utah       3
Ford in California   2
Ford in California   4

Column Data

Item            City
86 Corolla      Salt Lake
86 Corolla      Salt Lake
Toyota Corolla  Salt Lake City
Toyota Corolla  Salt Lake City
Toyota Corolla  Salt Lake City
F150            Los Angeles
F150            Los Angeles
Ford Taurus     San Francisco
Ford Taurus     San Francisco
Ford Taurus     San Francisco
Ford Taurus     San Francisco

SQL

select concat(
CASE 
 WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
 WHEN Item LIKE ('%Taco%') THEN 'Toyota'
 WHEN Item LIKE ('F%') THEN 'Ford'
ELSE Item
END,  -- END AS 'Brand' gives me a syntax error
' in ',
CASE
 WHEN City LIKE ("Salt Lake%") THEN 'Utah'
 WHEN City LIKE ("Las%") THEN 'California'
 WHEN City LIKE ("San%") THEN 'California'
ELSE City
END)

AS Purchase, count(*) as Total
FROM PurchaseOrders
GROUP BY Item, City
ORDER BY Purchase

Solution

Put the Purchase case expressions etc in a derived table (i.e. a FROM clause subquery). GROUP BY its result:

select Purchase, count(*)
from
(
    select concat(
    CASE 
     WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
     WHEN Item LIKE ('%Taco%') THEN 'Toyota'
     WHEN Item LIKE ('F%') THEN 'Ford'
    ELSE Item
    END,  -- END AS 'Brand' gives me a syntax error
    ' in ',
    CASE
     WHEN City LIKE ("Salt Lake%") THEN 'Utah'
     WHEN City LIKE ("Las%") THEN 'California'
     WHEN City LIKE ("San%") THEN 'California'
    ELSE City
    END) AS Purchase
    FROM PurchaseOrders
) dt
GROUP BY Purchase
ORDER BY Purchase