'Creating a New Column in query

Wonder if anyone could help me, I'm fairly new to query so am going a bit round in circles here.

I want to create a new column in the table, which is based on the Acquisition Type of a Lead Source eg if Lead Source = Facebook, Instagram, Twitter it will put Social as the acquisition Type, or if Lead Source = Adwords, Facebook Ads it will put PPC as the acquisition type.

Currently I have..

SELECT
    de."Lead Source" AS "Lead Source",
    COUNT(1) AS TOTAL
FROM
    "Deals" AS de 
WHERE    
    de."Created Time" BETWEEN '2020-01-01 00:00:00' AND  '2030-01-01 00:00:00'
GROUP BY  
    de."Lead Source"
sql


Solution 1:[1]

It sounds like you want a CASE statement. You can specify multiple WHEN condition THEN result pairs and the result of the whole statement is the result of the first condition to be true. You can also add an optional ELSE statement if none of the conditions match.

SELECT
    de."Lead Source" AS "Lead Source",
    COUNT(1) AS TOTAL,
    CASE 
        WHEN de."Lead Source" IN ('Facebook', 'Instagram', 'Twitter') 
            THEN 'Social'
        WHEN de."Lead Source" IN ('Adwords', 'Facebook Ads')
            THEN 'PPC'
        ELSE 'Unknown'
    END AS "Acquisition Type"
FROM  "Deals" AS  de 
WHERE    de."Created Time"  BETWEEN '2020-01-01 00:00:00'  AND  '2030-01-01 00:00:00'
GROUP BY  de."Lead Source"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D M

Related Questions