CODEWITHSUNDEEP
mysql
Tyra Pululi
Tyra Pululi

Reputation: 446

mysql join 3 tables and count 2

I have 3 tables (users, answers and questions) and need to count the total amount of users divided by registered and not registered that have finish all the answers of each day.

So I would have something like:

Total users completed day 1, total registered users completed day 1

Total users completed day 2, total registered users completed day 2

Total users completed day 3, total registered users completed day 3

Table structures are:

Users
Id, registered

Answers
id, user_id, question_id

Questions
id, day

And this is the query that i am working with.

select 
  a.user_id, a.question_id ,
  count(distinct q.day) as day
from answers a
left join questions q on q.id = a.question_id
left join users u on u.registered IS NOT NULL
group by u.id

Upvotes: 0

Views: 42

Answers (1)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6309

Try something like this:

SELECT q.day, COUNT(u.id) AS users, COUNT(u.registered) AS reg_users
  FROM answers a
  JOIN questions q ON q.id = a.question_id
  JOIN users u ON u.id = a.user_id
  GROUP BY q.day

EDIT

If you have multiple occurrence of users in a day, you should try something like this:

SELECT day, COUNT(id) AS users, COUNT(registered) AS reg_users
  FROM ( SELECT DISTINCT q.day, u.id, u.registered
           FROM answers a
           JOIN questions q ON q.id = a.question_id
           JOIN users u ON u.id = a.user_id ) qwert
  GROUP BY day

EDIT

Counting users, who have answered at least 15 questions the given day:

SELECT day, user_id, COUNT( DISTINCT question_id) AS users_15
  FROM answers a
  JOIN questions q ON q.id = a.question_id
  GROUP BY day, user_id
  HAVING COUNT( DISTINCT question_id) >= 15

Upvotes: 1

Related Questions