CODEWITHSUNDEEP
mysqlsqldatabase
mihaela
mihaela

Reputation: 404

MySQL count subquery on same table

I have this query: 

SELECT `facilities`.`name` AS facility, `countries`.`name` AS country, `states`.`name` AS state, `users`.`dosage` AS dosage, COUNT(`users`.`id`) AS registrations 
FROM `users` 
LEFT JOIN `countries` ON `users`.`country_id` = `countries`.`id` 
LEFT JOIN `states` ON `users`.`state_id` = `states`.`id` LEFT JOIN `user_facilities` ON `users`.`id` = `user_facilities`.`user_id` 
LEFT JOIN `facilities` ON `user_facilities`.`facility_id` = `facilities`.`id` 
WHERE `users`.`dosage` != "Not sure" 
GROUP BY `facilities`.`name`, `countries`.`name`, `states`.`name`, `users`.`dosage` 
ORDER BY `facilities`.`id` ASC, `countries`.`id` ASC, `states`.`name` ASC

It gives me this result:

Hospital  | United States | Arkansas | 10 doses  | 3    
Hospital  | United States | Arkansas | >10 doses | 4    
Home care | United States | Arkansas | 10 doses  | 1    
Home care | United States | Texas    | 10 doses  | 1

My users table: id | email | state_id | country_id | dosage | percent_completed

What I need to do is to get the number of users with percent_completed = 100 for each result. I tried subqueries, but I couldn't find the right subquery for this. I'm stuck. Does anyone have some piece of advice? Thanks.

Upvotes: 0

Views: 502

Answers (2)

Hakuna Matata
Hakuna Matata

Reputation: 761

After group by please add the " having sum(percent_completed) >=100 " that will will you users count who are completed > =100 percent

SELECT `facilities`.`name` AS facility, `countries`.`name` AS country, `states`.`name` AS state, `users`.`dosage` AS dosage, COUNT(`users`.`id`) AS registrations 
FROM `users` 
LEFT JOIN `countries` ON `users`.`country_id` = `countries`.`id` 
LEFT JOIN `states` ON `users`.`state_id` = `states`.`id` LEFT JOIN `user_facilities` ON `users`.`id` = `user_facilities`.`user_id` 
LEFT JOIN `facilities` ON `user_facilities`.`facility_id` = `facilities`.`id` 
WHERE `users`.`dosage` != "Not sure" 
GROUP BY `facilities`.`name`, `countries`.`name`, `states`.`name`, `users`.`dosage` having sum(users.percentage_completed) >= 100
ORDER BY `facilities`.`id` ASC, `countries`.`id` ASC, `states`.`name` ASC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I understand correctly, you can use conditional aggregation. In MySQL you can just do:

select . . .,
       sum(percent_completed = 100) as num_at_100

Upvotes: 2

Related Questions