CODEWITHSUNDEEP
mysqlsql
Rey Chabby Estrera
Rey Chabby Estrera

Reputation: 11

how to use a count case as a condition in an sql query

How can I use the count case when to be condition of an sql query to search.

SELECT Branches.name AS Branches_name, Branches.code AS Branches__code ,
(COUNT(CASE WHEN products_carried_per_branches.carried = 1 THEN 1 END )) AS carried , 
(COUNT(CASE WHEN products_carried_per_branches.carried = 0 THEN 1 END )) AS unCarried
FROM branches INNER JOIN products_carried_per_branches ON (products_carried_per_branches.company_id = branches.company_id
AND products_carried_per_branches.branch_code = branches.code )
WHERE (
Branches.company_id =200017
AND Branches.deleted =0
AND products_carried_per_branches.deleted =0`

AND (COUNT(CASE WHEN products_carried_per_branches.carried = 1 THEN 1 END )) = 35

GROUP BY code

this is what i got so far but. but this got an "invalid use of group function" sql error. I hope someone can help me here. I also have stumbled the having statement but prefer not to use it because I want to use this code as a search in a datatable.

Upvotes: 1

Views: 42

Answers (1)

A.Steer
A.Steer

Reputation: 411

You would want to add a having clause as a count is an aggregate function. So your code would be

SELECT 
Branches.name AS Branches_name, 
Branches.code AS Branches__code , 
(COUNT(CASE WHEN products_carried_per_branches.carried = 1 THEN 1 END )) AS carried,  (COUNT(CASE WHEN products_carried_per_branches.carried = 0 THEN 1 END )) AS unCarried 
FROM 
branches INNER JOIN products_carried_per_branches ON (products_carried_per_branches.company_id = branches.company_id AND products_carried_per_branches.branch_code = branches.code ) WHERE ( Branches.company_id =200017 AND Branches.deleted =0 AND products_carried_per_branches.deleted =0

GROUP BY code

Having (COUNT(CASE WHEN products_carried_per_branches.carried = 1 THEN 1 END )) = 35

Upvotes: 1

Related Questions