Re: Categorize values of columns

[Phillip Ward]

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.