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.