sqlhive

How to iterate through column A to sum column B conditionally?


Given this original table Record:

food   | food_preference | count
--------------------------------
burger | [burger]        | 100
burger | [burger, pizza] | 70
pizza  | [burger, pizza] | 130
burger | [burger, corn]  | 25
corn   | [burger, corn]  | 25

How do I iterate through all unique food values to sum the count of food in food_preference to give:

food   | count
--------------
burger | 350
pizza  | 200
corn   | 50

I tried to do a case matching but not sure how to iterate through the food values.

WITH Record AS
(
    SELECT * FROM (
        values 
            ('burger', '[burger]', 100), 
            ('burger', '[burger, pizza]', 70), 
            ('pizza', '[burger, pizza]', 130), 
            ('burger', '[burger, corn]', 25), 
            ('corn', '[burger, corn]', 25)
    ) x(food, food_preference, count)
)

SELECT 
    food,
    CASE
        -- How to sum the count value if the food is in the food_preference? 
    END AS total_count
FROM Record

Solution

  • Using STRING_SPLIT will do what you want.

    SELECT value AS food
         , SUM(x.count) AS count
    FROM record AS x
        CROSS APPLY string_split(REPLACE(REPLACE(x.food_preference, '[', ''), ']', ''), ',') AS z
    GROUP BY value;
    

    DBFiddle