Mysql count and return just one row of data

Question

I need to count the amount of users that have have answered all of those 3 profile_options (so they have at least 3 records in the profile_answers table).

SELECT COUNT(DISTINCT(users.id)) users_count
FROM users
INNER JOIN profile_answers ON profile_answers.user_id = users.id
WHERE profile_answers.profile_option_id IN (37,86,102) 
GROUP BY users.id
HAVING COUNT(DISTINCT(profile_answers.id))>=3

The problem is that this query is return a table with rows for each user and how many they answered (in this case always 3). What I need is to return just one row that has the total number of users (so the sum of all rows of this example)

I know how to do it with another subquery but the problem is that I am running into "Mysql::Error: Too high level of nesting for select"

Is there a way to do this without the extra subquery?

SELECT SUM(sum_sub.users_count) FROM (
 (SELECT COUNT(DISTINCT(users.id)) users_count
 FROM users
 INNER JOIN profile_answers ON profile_answers.user_id = users.id
 WHERE profile_answers.profile_option_id IN (37,86,102) 
 GROUP BY users.id
 HAVING COUNT(DISTINCT(profile_answers.id))>=3)
) sum_sub

Answer 1

Please give this query a shoot

SELECT COUNT(DISTINCT(u.id)) AS users_count 
FROM users AS u
INNER JOIN (
     SELECT user_id, COUNT(DISTINCT profile_option_id) AS total
     FROM profile_answers
     WHERE profile_option_id IN (37,86,102) 
     GROUP BY users.id 
     HAVING  COUNT(DISTINCT profile_option_id) = 3
) AS a ON a.user_id = u.id

If you have lots of data in your tables, you will get a better/faster performance by using temporary tables like so

CREATE TEMPORARY TABLE a (KEY(user_id)) ENGINE = MEMORY
SELECT user_id, COUNT(DISTINCT profile_option_id) AS total
FROM profile_answers
WHERE profile_option_id IN (37,86,102) 
GROUP BY users.id 
HAVING  COUNT(DISTINCT profile_option_id) = 3;

Then your final query will look like this

SELECT COUNT(DISTINCT(u.id)) as users_count 
FROM a 
INNER JOIN on a.user_id = u.id

Unless there is a need to join the users table you can go with this

SELECT COUNT(*) AS users_count
FROM (
     SELECT user_id, COUNT(DISTINCT profile_option_id) AS total
     FROM profile_answers
     WHERE profile_option_id IN (37,86,102) 
     GROUP BY users.id 
     HAVING  COUNT(DISTINCT profile_option_id) = 3
) AS a

Should you need another solution, please consider providing us you EXPLAIN EXTENDED for the query and the table definitions along with a better problem description.

I hope this helps

Answer 2

You can give the queries a name using the AS clause. See the updated query below.

SELECT SUM(sum_sub.users_count) FROM (
(SELECT COUNT(DISTINCT(users.id)) as users_count
 FROM users
 INNER JOIN profile_answers ON profile_answers.user_id = users.id
 WHERE profile_answers.profile_option_id IN (37,86,102) 
 GROUP BY users.id
 HAVING COUNT(DISTINCT(profile_answers.id))>=3)
) as sum_sub

Answer 3

You should not group by on a field not present in select statement.

select id, count(*) from users group by id is fine

select count(id) from users group by id is NOT

Regarding your query I think the link to user table is not necessary. Just using foreign key should be fine.

Try this one:

select count(*) from 
(SELECT users_id count(*) as cnt
FROM profile_answers
INNER JOIN users ON profile_answers.user_id = users.id
WHERE profile_answers.profile_option_id IN (37,86,102) 
group by users_id
having count(*) >3)