sql count query

[dps]

Hello,

What do I want : Out of all the products in this system need to search for at least one which is featured in more then 10 commercials (i.e. product master block and commercial detail)

I have an idea of the sql statement but not quite right - need a having count(*), group by???

I am testing a fucntion and hence need this info.

Mucha ppreciated

 

[lewisp]

SELECT count(*),product_id
FROM   commercials
GROUP BY product_id
having count(*) > 10;

You can add your own links to get product details from the IDs.

 

[dps]

But will this give a product which has featured in more then 10 commericial. should I not be need two tables? One which is the product_name from products and the other commercials (which has a prd_code link column).

 

[lewisp]

You can do that using my query above, just provide the links and columns that you want to see.

 

[dps]

would this not be more closer?

SELECT product_id
FROM product p, commercials c
WHERE product_join = commercial_join
AND exists (select count(*), c.com_num_code from commercial
group by c.com_num_code
having count(*) > 10)

By this way it'll go through to commercial table and bring back rows that have more then 10 commercials belonging to that product?

 

[carp]

If commercial is an intersect of product and commercials, and product_id is common to both product and commercial, then

SELECT product_id
FROM commercial
GROUP BY product_id
HAVING count(*) > 10;

should give you what you want.

On the other hand, if commerial and commercials are really the same table and you just have a typo, then lewisp's query will work fine.

BTW, I believe your query is flawed - you have no linkage between your main query and your subquery.

Elbert, CO
0921MDT

 

[dps]

Oops! oversight