MySQL Forums
Forum List  »  Newbie

Categorize values of columns
Posted by: Nirgal Nirgal
Date: August 12, 2022 12:32AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Categorize values of columns
August 12, 2022 12:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.