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
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