0

I'm building a survey system where

  • A client that has many users
  • A user can have multiple surveys.

I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) way to see how many of the users for client 10, have a completion in the survey_completion table

SELECT
survey_id,
(SELECT COUNT(*) FROM users u WHERE u.client_id = 10) as users,
(SELECT COUNT(*) FROM survey_completions sc INNER JOIN users u ON u.id = sc.user_id WHERE u.client_id = 10) as num_users_completed
FROM survey_completions sc
INNER JOIN users u ON u.id = sc.user_id
WHERE u.client_id = 10
GROUP BY sc.survey_id
  • The output that your query will produce doesn't quite match what I would expect it to be based on the verbal description of the problem. After reading the description I assumed you wanted a single row with the total number of client 10's users and the number of those with completed surveys. Your query, however, suggests a different output. Perhaps you could clarify what kind of output you expect. Even better if you could add a data sample and the expected output for that specific sample, cheers. – Andriy M Feb 11 at 14:12
  • Hi @AndriyM I expected what you said above, so to see for client 10, which users have a row in survey_completions. – Notflip Feb 11 at 16:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.