CODEWITHSUNDEEP
mysql
Linesofcode
Linesofcode

Reputation: 5891

Get total rows using HAVING to condition

The following query returns the results limited to 20 rows and where there is no filter id 6 applied.

SELECT p.id, p.description
FROM   product p
LEFT JOIN product_filter pf 
ON pf.product_id = p.id
WHERE  p.status = TRUE
GROUP  BY p.id
HAVING SUM(pf.filter_id = '6') = 0 
ORDER  BY p.description ASC 
LIMIT  0, 20

This works great, but now I'd like to count the amount of rows with the same condition.

SELECT COUNT(DISTINCT p.id) AS total 
FROM product p
LEFT JOIN product_filter pf 
ON pf.product_id = p.id
WHERE p.status = TRUE
HAVING SUM(pf.filter_id = 6) = 0

And this query returns me zero results.

EDIT: As requested, SQLFiddle to help view the problem in a more detailed way.

Upvotes: 0

Views: 31

Answers (2)

Linesofcode
Linesofcode

Reputation: 5891

Although the @Raymond Nijland answer is the correct, in my environment the code he suggested did not work.

By taking a look at this post COUNT(*) returning multiple rows instead of just one I manage to make it work by doing:

SELECT COUNT(DISTINCT p.id) AS total 
FROM (SELECT p2.id 
      FROM product AS p2 
      LEFT JOIN product_filter pf 
        ON pf.product_id = p2.id 
      GROUP BY p2.id 
      HAVING SUM(pf.filter_id = '6') = 0) As p

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

You missed GROUP BY p.id. HAVING without GROUP BY doesn't work. 

SELECT COUNT(DISTINCT p.id) AS total 
FROM product p
LEFT JOIN product_filter pf 
ON pf.product_id = p.id
WHERE p.status = TRUE
GROUP BY p.id
HAVING SUM(pf.filter_id = 6) = 0

Demo http://sqlfiddle.com/#!9/00b12/6

Upvotes: 2

Related Questions