Categorize values of columns
Hello
I have a table like this, with a given number of flavors in which '1' represents the flavor is on shelf and '0' represents that is not on shelf (the example is way simpler than the real thing)
Chocolate Peanut Black chocolate Strawberry Retailer Pointofsales
1 0 1 0 7 eleven 28
0 1 0 0 Family mart 45
1 0 0 0 Walmart 22
1 0 0 1 Carrefour 200
Basically i want to find out at a glance how many Point of sales each flavours has.
Now this can be done easily in excel simply using filters, but i would like this to be done by code without any extra work each time the information is updated.
Before i tried this: (ignore the numbers after the flavours and other flavours they dont matter for this)
----------------------------------------------------------------------------
ALTER TABLE listing_analysis_20220720 ADD COLUMN Product VARCHAR(50) AFTER Listing_fee_RMB;
UPDATE listing_analysis_20220720 SET Product = '30pc Strawberry'
WHERE Choco_30 = '0'
AND Suave_30 = '0'
AND Black_30 = '0'
AND Strawberry_30 = '1'
AND Choco_Bowl_15 = '0'
AND Straw_Bowl_15 = '0'
AND Lock_straw_15 = '0'
AND Lock_black_15 = '0'
AND Lock_choco_15 = '0';
UPDATE listing_analysis_20220720 SET Product = 'Choco, Suave, Black, Strawberry'
WHERE Choco_30 = '1'
AND Suave_30 = '1'
AND Black_30 = '1'
AND Strawberry_30 = '1'
AND Choco_Bowl_15 = '0'
AND Straw_Bowl_15 = '0'
AND Lock_straw_15 = '0'
AND Lock_black_15 = '0'
AND Lock_choco_15 = '0';
And so on...
----------------------------------------------------------------------------
This works to create a "Product" column that will tell me what are the products in that retailer. But i have a couple of problems with my solution.
1. Its really annoying to write code for each specific scenario. the more type of flavours you have the more possibilities you have to consider.
2. I cant count how many point of sales i have for each flavour separately, each time i want to count POS products i will end up with the flavours grouped in categories like this.
Product Point of sales
Choco, Black 300
30pc chocolate 500
No Sku 12
Has Repackaged 20
Strawberry 10
Black, Strawberry 12
Choco, Strawberry 13
Choco, Black, Strawberry 300
Choco, Suave, Black, Strawberry
Choco, Suave, Black 200
But what i would like to see is how many point of sales in total each specific Flavour has.
Sorry if i am not expressing myself clearly, If you need to ask any questions to understand mine better please let me know.