MySQL Forums
Forum List  »  Newbie

Re: Categorize values of columns
Posted by: Phillip Ward
Date: August 16, 2022 02:17AM

Quote

... Would this filter not also include this flavor if there is a retailer with 11, 44 and 14?

The in clause does not define a range of value.
It defines a number of discrete values, any one of which will be included.

If you want to include 14 as well, you'd have to explicitly list it within the in clause, as in:

where Flavour in ( 11, 44, 14 )    // Items to not have to appear in order

The "range" operator you're thinking of is between:

where Flavour between 11 and 44

Quote

Also I find this part a bit confusing .. group by ... having ...

The having clause is just like a where clause, but it acts on the aggregated values that come out of the group by (as opposed to the where clause that works on the individual rows going into the group by).

To demonstrate, let's build this query up step-by-step:

// Raw data 
select 
  Retailer 
, Flavour 
from stock ; 

+-------------+---------+
| Retailer    | Flavour | 
+-------------+---------+
| 7 eleven    |      11 | 
| 7 eleven    |      33 | 
| Family mart |      22 | 
| Walmart     |      11 | 
| Carrefour   |      11 | 
| Carrefour   |      44 | 
+-------------+---------+

// Now filter by the two flavours we're interested in 
select 
  Retailer 
, Flavour 
from stock 
where Flavour in ( 11, 44 )
order by 
  Retailer
, Flavour ; 

+-------------+---------+
| Retailer    | Flavour | 
+-------------+---------+
| 7 eleven    |      11 | 
| Carrefour   |      11 | 
| Carrefour   |      44 | 
| Walmart     |      11 | 
+-------------+---------+

// Aggregate by Retailer 
select 
  Retailer 
, count( * ) 
from stock 
where Flavour in ( 11, 44 )
group by Retailer
order by Retailer ; 

+-------------+----------+ 
| Retailer    | count(*) | 
+-------------+----------+ 
| 7 eleven    |        1 | 
| Carrefour   |        2 | 
| Walmart     |        1 | 
+-------------+----------+ 

// And finally, use "having" as a "where" on the counts
select 
  Retailer 
, count( * ) 
from stock 
where Flavour in ( 11, 44 )
group by Retailer
having count( * ) = 2
order by Retailer ; 

+-------------+----------+ 
| Retailer    | count(*) | 
+-------------+----------+ 
| Carrefour   |        2 | 
+-------------+----------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Categorize values of columns
August 16, 2022 02:17AM


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.