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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.