SQL: COUNT() with items appearing multiple times in one-to-many relationship? Alternative to GROUP BY?

Question

I have a query listing product categories, by supercategory > category. The query counts the number of products in each category (COUNT(ptc.catid)).

Howewer: the query doesn't let a category appear twice (a category can be linked to multiple supercategories). How can this be re-written so categories (c.title) can be listed under multiple supercategories?

SELECT 
 s.supercategory_id, 
            s.title, 
            c.title, 
            c.id, 
            COUNT(ptc.catid)

FROM supercategories s, 
     supercategories_to_categories stc, 
     categories c,
     products p,
     products_categories ptc

WHERE  c.viewable='1'

AND    c.id=stc.category_id 
AND    stc.supercategory_id=s.supercategory_id 
AND    ptc.catid=c.id 
AND    ptc.productid = p.id
AND    p.viewable='y'

GROUP BY ptc.catid
ORDER BY s.title, c.title

Thanks!

Answer 1

The join syntax you're using is a bit "old school", so if you don't mind I'll rewrite it here:

select
    s.supercategory_id,
    max(s.title) as supercategory_title,
    max(c.title) as category_title,
    c.id,
    count(ptc.catid) as product_count

from supercategories s

join supercategories_to_categories stc on stc.supercategory_id = s.supercategory_id
join categories c on c.id = stc.category_id
join products_to_categories ptc on ptc.catid = c.id
join products p on p.id = ptc.productid

where p.viewable = 'y'

group by s.supercategory_id, c.id

As shown, I've changed the group by expression to include supercategory_id, making it possible for a category to show up once per super category that it's a member of. I also added aggregation functions around the non-group-by columns, as I'm not sure how that would have executed before.

Answer 2

Try changing your GROUP BY to:

GROUP BY ptc.catid, stc.supercategory_id