MySQL Forums
Forum List  »  Newbie

Re: Categorize values of columns
Posted by: Phillip Ward
Date: August 12, 2022 05:04AM

This arrangement breaks basis Data Normalisation rules.

You need something more like this:

select * from flavours ; 

+----+-----------------+
| id | name            | 
+----+-----------------+
| 11 | Chocolate       | 
| 22 | Peanut          | 
| 33 | Black Chocolate | 
| 44 | Strawberry      |
+----+-----------------+

select * from stock ; 

+-------------+-----+---------+
| Retailer    | PoS | Flavour | 
+-------------+-----+---------+
| 7 eleven    | 28  |      11 | 
| 7 eleven    | 28  |      33 | 
| Family mart | 45  |      22 | 
| Walmart     | 22  |      11 | 
| Carrefour   | 200 |      11 | 
| Carrefour   | 200 |      44 | 
+-------------+-----+---------+

Now your question - "i want to find out at a glance how many Point of sales each flavours has" - becomes far simpler:

 
select 
  s.Flavour
, count( s.PoS ) as tally
from stock s 
group by s.flavour 
order by s.flavour; 

+---------+-------+
| flavour | tally | 
+---------+-------+
|      11 |     3 | 
|      22 |     1 | 
|      33 |     1 | 
|      44 |     1 | 
+---------+-------+

But that only shows you the Flavour's identifier, not the name, which would be much more readable, so we have to join this result to the Flavours table as well:

 
select 
  f.name
, t0.tally 
from flavours f 
inner join 
( select 
    s.Flavour
  , count( s.PoS ) as tally
  from stock s 
  group by s.flavour 
) as t0 
on f.id = t0.Flavour 
order by f.name ; 

+-----------------+-------+
| name            | tally | 
+-----------------+-------+
| Black Chocolate |     1 | 
| Chocolate       |     3 | 
| Peanut          |     1 | 
| Strawberry      |     1 | 
+-----------------+-------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Categorize values of columns
August 12, 2022 05:04AM


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.