This should be simple but I'm missing something. If at all possible can
somebody please hint how I should be writing the following query
(mySQL).
I need to query the total count of products that meet all the selected
categories.

-----------------------------------
cat | product
-----------------------------------
1 1
2 1
1 2
1 3
2 3

Q: so how many products are ***igned to both cat 1&2

Thanks in advance.
Mark Dyer
NZ

EMAIL REMOVED wrote:

> This should be simple but I'm missing something. If at all possible can
> somebody please hint how I should be writing the following query
> (mySQL).
> I need to query the total count of products that meet all the selected
> categories.
>
> -----------------------------------
> cat | product
> -----------------------------------
> 1 1
> 2 1
> 1 2
> 1 3
> 2 3
>
> Q: so how many products are ***igned to both cat 1&2
>
> Thanks in advance.
> Mark Dyer
> NZ
Does the 3 in the product column in the last row mean that there are 3
products for that row?

I think (but not tested)

SELECT SUM(product) FROM <TABLE> GROUP BY cat



Captain Paralytic wrote:
> EMAIL REMOVED wrote:
>
> > This should be simple but I'm missing something. If at all possible can
> > somebody please hint how I should be writing the following query
> > (mySQL).
> > I need to query the total count of products that meet all the selected
> > categories.
> >
> > -----------------------------------
> > cat | product
> > -----------------------------------
> > 1 1
> > 2 1
> > 1 2
> > 1 3
> > 2 3
> >
> > Q: so how many products are ***igned to both cat 1&2
> >
> > Thanks in advance.
> > Mark Dyer
> > NZ
> Does the 3 in the product column in the last row mean that there are 3
> products for that row?

"EMAIL REMOVED" <EMAIL REMOVED> wrote in
news:EMAIL REMOVED ups.com:

> Q: so how many products are ***igned to both cat 1&2

USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (cat TINYINT UNSIGNED NOT NULL, product TINYINT UNSIGNED
NOT NULL);
INSERT INTO foo (cat, product) VALUES
(1, 1), (2, 1), (1, 2), (1, 3), (2, 3);

SELECT COUNT(*)
FROM (
SELECT product
FROM foo
WHERE cat IN (1, 2)
GROUP BY product
HAVING COUNT(DISTINCT cat) = 2
) T;

--
felix