CODEWITHSUNDEEP
mysql
rockstardev
rockstardev

Reputation: 13527

MySQL Aggreation query?

I have the following mySQL code: 

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

I want to be able to include another COUNT column. But this must only be counted as as the products with p.state = "active". Here's my INCORRECT solution

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      COUNT(q.productid) as totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

Any help? I have no idea where to go from here...

Upvotes: 2

Views: 65

Answers (3)

Sass
Sass

Reputation: 560

This works but maybe doesn't align with the left join approach so much:

SELECT        
c.catId,c.catName,
COUNT(p.productId) as totalProdsInCategory,
(SELECT count(*) from product as p where p.catId=1 AND p.state='active') as totalActive
FROM cat as c      
LEFT JOIN product as p ON c.catId = p.catId 
WHERE c.catId = 1     
GROUP BY c.catId     
ORDER BY c.catName ASC

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85046

Try this:

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      SUM(IF(p.state='active',1,0)) as totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

This is similar to @Conrad's but a little cleaner in my opinion

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52645

You can use SUM (CASE WHEN p.state = "active" THEN 1 ELSE 0 END) to get the count you're looking for.

SELECT 
      c.categoryId, 
      c.categoryName, c.categoryParent, 
      c.categoryDescription, 
      COUNT(p.productid) as totalProdsInCategory, 
      SUM (CASE WHEN p.state = "active" THEN 1 ELSE 0 END) totalActiveProdsInCategory
    FROM categories as c
     LEFT JOIN normalproducts as p 
     ON c.categoryId = p.categoryid
WHERE c.categoryId = 41
    GROUP BY c.categoryId
    ORDER BY c.categoryParent ASC,  c.categoryName ASC

Upvotes: 5

Related Questions