CODEWITHSUNDEEP
sqlsql-serverconcatenationdistinct
Scott
Scott

Reputation: 43

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

Upvotes: 0

Views: 698

Answers (3)

jarlh
jarlh

Reputation: 44795

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

Upvotes: 1

Dave O'Gorman
Dave O'Gorman

Reputation: 1

The issue is in the 'group by'. You should be able to group by the purchase field that you are generating:

select 
    concat(
        CASE 
            WHEN Item LIKE ('%Corolla%') THEN 'Toyota'
            WHEN Item LIKE ('%Taco%') THEN 'Toyota'
            WHEN Item LIKE ('F%') THEN 'Ford'
            ELSE Item
        END,
        ' 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 
    Purchase
ORDER BY 
    Purchase

Upvotes: 0

Bohemian
Bohemian

Reputation: 425278

Change

GROUP BY Item, City

To

GROUP BY 1

Which groups by column number 1.

You were grouping by multiple columns that your expression rendered to the same value, but had different value combinations and so were returned as separate rows.

Upvotes: 0

Related Questions