MYSQL query for getting no of record with multiple category-CodePudding

So I have 1 table jobcatlist with 2 columns certification_id and JobCategory_id 1 certification_id can have multiple Jobcategories like (Preschool 1, Primary 2, Intermediate 3, Middle 4)

I prepared the 1st query for getting the total no of certifications with multiple categories this query

SELECT certification_id, COUNT(certification_id) AS cert_count
FROM jobcatlist 
GROUP BY certification_id
HAVING COUNT(certification_id) > 1

and it looks correct to me, please let me know if it's not

so I got stuck with my second problem where I want to get the no of certifications with multiple category where specific Category is there like Preschool 1

I am trying this query but its not correct

SELECT certification_id, COUNT(certification_id) AS cert_count
FROM jobcatlist 
WHERE jobcategory_id = 1
GROUP BY certification_id
HAVING  COUNT(certification_id) > 1 
ORDER BY certification_id DESC

CodePudding user response：

The WHERE clause:

WHERE jobcategory_id = 1

filters out all other jobcategory_ids, so, assuming that the combination of certification_id and jobcategory_id is unique, COUNT() always returns 1.

Remove the WHERE clause and add the condition in the HAVING clause:

SELECT certification_id, 
       COUNT(*) AS cert_count
FROM jobcatlist 
GROUP BY certification_id
HAVING cert_count > 1 AND SUM(jobcategory_id = 1) > 0 
-- or HAVING cert_count > 1 AND MAX(jobcategory_id = 1) = 1
ORDER BY certification_id DESC;