Hello,

I am trying to count the number of customers for each product group.
One customer may have multiple products. Now I am doing it one by one,
i.e., when I do product_id like '111%', I make all other 3 groups
comments (please see query below). Is there a way that I can get the #s
of the 4 groups at the same time? In fact, I have more than 10 groups,
feel cumbersome to do it one by one. Many thanks!

SELECT count(distinct customer_id)
FROM account
WHERE year(close_date)=1900
and product_id like '111%'
--Product Group 1
--and product_id in ('222', '333')
--Product Group 2
--and product_id in ('444', '555')
--Product Group 3
--and product_id like '666%'
--Product Group 4

On 9 Mar 2005 13:19:51 -0800, rong.guo@gmail.com wrote:

>Hello,
>
>I am trying to count the number of customers for each product group.
>One customer may have multiple products. Now I am doing it one by one,
>i.e., when I do product_id like '111%', I make all other 3 groups
>comments (please see query below). Is there a way that I can get the #s
>of the 4 groups at the same time? In fact, I have more than 10 groups,
>feel cumbersome to do it one by one. Many thanks!
>
>SELECT count(distinct customer_id)
>FROM account
>WHERE year(close_date)=1900
>and product_id like '111%'
> --Product Group 1
>--and product_id in ('222', '333')
> --Product Group 2
>--and product_id in ('444', '555')
> --Product Group 3
>--and product_id like '666%'
> --Product Group 4

Hi rong.guo,

Try if this works:

SELECT ProductGroup, COUNT(DISTINCT customer_id)
FROM (SELECT customer_id,
CASE
WHEN product_id LIKE '111%' THEN 1
WHEN product_id IN ('222', '333') THEN 2
WHEN product_id IN ('444', '555') THEN 3
WHEN product_id LIKE '666%' THEN 4
END AS ProductGroup
FROM account
WHERE year(close_date) = 1900) AS x
GROUP BY ProductGroup

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Thanks Hugo! It works well!