Md Dang Md Dang - 2 months ago 7
SQL Question

Combining multiple Counts

I'm Trying to combine this set of

queries
.
I have done the search but my
queries
has some
joins
, it's getting complicated, can somebody help?

select count(DISTINCT p.products_id) as count from (products p)
join (products_to_categories p2c)
on (p.products_id = p2c.products_id)
left join (specials s)
on (p.products_id = s.products_id)
left join (products_attributes pa)
on (p.products_id = pa.products_id)
left join (products_options_values pv)
on (pa.options_values_id = pv.products_options_values_id)
left join (products_stock ps)
on (p.products_id=ps.products_id and pv.products_options_values_id = ps.products_options_values_id2)
INNER JOIN products_specifications ps10 ON p.products_id = ps10.products_id INNER JOIN products_specifications ps17 ON p.products_id = ps17.products_id where p.products_status = '1' and ps.products_stock_quantity>0 and p2c.categories_id in (72,1,23,47,48,49,74,24,33,34,35,77,25,46,45,44,40,41,42,43,76,78,83,50,52,81,79,2,54,60,82,53,57,58,55,61,62,63,75,56,64,65,66,67,68,69,70,71,84,80) AND ps10.specification in ('Meisje') AND ps10.specifications_id = '10'
AND ps10.language_id = '1'
and products_options_values_name in ( 80,"18M/80cm","8-12 mnd","9m","12m","9-12m","9M","12M" ) AND ps17.specification in ('Overhemd', 'Polo') AND ps17.specifications_id = '17'
AND ps17.language_id = '1'





select count(DISTINCT p.products_id) as count from (products p)
join (products_to_categories p2c)
on (p.products_id = p2c.products_id)
left join (specials s)
on (p.products_id = s.products_id)
left join (products_attributes pa)
on (p.products_id = pa.products_id)
left join (products_options_values pv)
on (pa.options_values_id = pv.products_options_values_id)
left join (products_stock ps)
on (p.products_id=ps.products_id and pv.products_options_values_id = ps.products_options_values_id2)
INNER JOIN products_specifications ps10 ON p.products_id = ps10.products_id INNER JOIN products_specifications ps17 ON p.products_id = ps17.products_id where p.products_status = '1' and ps.products_stock_quantity>0 and p2c.categories_id in (72,1,23,47,48,49,74,24,33,34,35,77,25,46,45,44,40,41,42,43,76,78,83,50,52,81,79,2,54,60,82,53,57,58,55,61,62,63,75,56,64,65,66,67,68,69,70,71,84,80) AND ps10.specification in ('Jongen') AND ps10.specifications_id = '10'
AND ps10.language_id = '1'
and products_options_values_name in ( 80,"18M/80cm","8-12 mnd","9m","12m","9-12m","9M","12M" ) AND ps17.specification in ('Overhemd', 'Polo') AND ps17.specifications_id = '17'
AND ps17.language_id = '1'





select count(DISTINCT p.products_id) as count from (products p)
join (products_to_categories p2c)
on (p.products_id = p2c.products_id)
left join (specials s)
on (p.products_id = s.products_id)
left join (products_attributes pa)
on (p.products_id = pa.products_id)
left join (products_options_values pv)
on (pa.options_values_id = pv.products_options_values_id)
left join (products_stock ps)
on (p.products_id=ps.products_id and pv.products_options_values_id = ps.products_options_values_id2)
INNER JOIN products_specifications ps10 ON p.products_id = ps10.products_id INNER JOIN products_specifications ps17 ON p.products_id = ps17.products_id where p.products_status = '1' and ps.products_stock_quantity>0 and p2c.categories_id in (72,1,23,47,48,49,74,24,33,34,35,77,25,46,45,44,40,41,42,43,76,78,83,50,52,81,79,2,54,60,82,53,57,58,55,61,62,63,75,56,64,65,66,67,68,69,70,71,84,80) AND ps10.specification in ('Unisex') AND ps10.specifications_id = '10'
AND ps10.language_id = '1'
and products_options_values_name in ( 80,"18M/80cm","8-12 mnd","9m","12m","9-12m","9M","12M" ) AND ps17.specification in ('Overhemd', 'Polo') AND ps17.specifications_id = '17'
AND ps17.language_id = '1'

Answer

I have come up with this and seems to work

The output is

count1   count2   count3
------------------------
  10        0        2

@john bingham "your queries contain a bit of redundancy"

Can you help me make this query a bit less redundancy?

select 
    count(DISTINCT if(ps10.specification in ('Meisje'),p.products_id,NULL)) as count1,
    count(DISTINCT if(ps10.specification in ('Jongen'),p.products_id,NULL)) as count2,
    count(DISTINCT if(ps10.specification in ('Unisex'),p.products_id,NULL)) as count3
from 
    (products p)
join 
    (products_to_categories p2c) on (p.products_id = p2c.products_id)
left join 
    (specials s) on (p.products_id = s.products_id)
left join 
    (products_attributes pa) on (p.products_id = pa.products_id)
left join 
    (products_options_values pv) on (pa.options_values_id = pv.products_options_values_id)
left join 
    (products_stock ps) on (p.products_id = ps.products_id 
                        and pv.products_options_values_id = ps.products_options_values_id2)       
inner join
    products_specifications ps10 ON p.products_id = ps10.products_id  
where 
    p.products_status = '1' 
    and ps.products_stock_quantity > 0   
    and p2c.categories_id in (72,1,23,47,48,49,74,24,33,34,35,77,25,46,45,44,40,41,42,43,76,78,83,50,52,81,79,2,54,60,82,53,57,58,55,61,62,63,75,56,64,65,66,67,68,69,70,71,84,80)  
    and ps10.specifications_id = '10'
    and ps10.language_id = '1'
Comments