MySQL Forums
Forum List  »  Newbie

Re: Categorize values of columns
Posted by: Phillip Ward
Date: August 15, 2022 03:58AM

Quote

In the example you put, each retailer appears to have a single flavour.

No, it doesn't.
Look again:

+-------------+-----+---------+
| Retailer    | PoS | Flavour | 
+-------------+-----+---------+
| 7 eleven    | 28  |      11 | ) 1 retailer, 2 flavours 
| 7 eleven    | 28  |      33 | )
| Family mart | 45  |      22 | 
| Walmart     | 22  |      11 | 
| Carrefour   | 200 |      11 | ) 1 retailer, 2 flavours
| Carrefour   | 200 |      44 | )
+-------------+-----+---------+

Both "7 eleven" and "Carrefour" have two flavours, "Family mart" and "Walmart" have only one.
Multiple flavours for a given retailer are held in multiple rows, not multiple columns.

Quote

... I would like to know, how many retailers have chocolate (regardless of weather they also have other flavours) ...

That's done with a simple "where" clause on Flavour:

select Retailer 
from stock 
where Flavour = 11 // the id for Chocolate 
order by Retailer ; 

+-------------+
| Retailer    |
+-------------+
| 7 eleven    |
| Carrefour   |
| Walmart     |
+-------------+

Quote

... also I would like to know how many retailers have both chocolate and strawberry.

OK, slightly more complex, but this should do it:

select Retailer 
from stock 
where Flavour in ( 11, 44 )
group by Retailer
having count( * ) = 2 
order by Retailer ; 

+-------------+
| Retailer    |
+-------------+
| Carrefour   |
+-------------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Categorize values of columns
August 15, 2022 03:58AM


Sorry, only registered users may post in this forum.

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.