MySQL Compare row count to see which user has a row in the other table

21
February 12, 2019, at 07:50 AM

I'm building a survey system where

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

Client A might have users [1,2,3]. Now there's a table user_completions, which shows which users have completed my survey.

users table
+----+-----------+
| id | client_id |
+----+-----------+
|  1 |         1 |
|  2 |         1 |
+----+-----------+

user_completions table
+---------+-----------+
| user_id | survey_id |
+---------+-----------+
|       1 |         1 |
|       2 |         2 |
+---------+-----------+

How can I (in one query) see how many users have a record in the user_completion table, grouped by survey, and only for the current client

The desired result would look something like

+-----------+-------+-----------------------+
| survey_id | users | num_persons_completed |
+-----------+-------+-----------------------+
|         1 |     2 |         1             |
|         2 |     2 |         1             |
+-----------+-------+-----------------------+
Answer 1

I think you're missing couple of table mentioning in your question but if you really have only these 2 tables than you can achieve this with following query:

select tmp.survey_id, count(distinct tmp.user_id) completed, 
      (select count(distinct id) from users where client_id = 1) users
from
(select t1.*, t2.*
from users t1 
join user_completions t2
on t1.id = t2.user_id
where t1.client_id = 1
)tmp
group by tmp.survey_id
READ ALSO
How to get unique records with join tables and without using group by

How to get unique records with join tables and without using group by

I want to get unique record from 2 tables without using Group By instead of i want to use DISTINCT because when i used group by it does not return last updated records

18
Convert NOT IN to LEFT JOIN

Convert NOT IN to LEFT JOIN

I have this query:

47
How take record with last date in MySQL?

How take record with last date in MySQL?

In MySQL database I have table with such structure:

26
Insert multiple Scrapy data into mysql

Insert multiple Scrapy data into mysql

I have done to scrapy one of website then it also able to adapt to multiple pageBut when I want to insert into database, there is difficulty that I dont know

14