CODEWITHSUNDEEP
mysqlcountsubquery
CairoCoder
CairoCoder

Reputation: 3189

Using Count with Count Distinct and Group By

I have two tables, one for employees which has id, name and company columns and another table for survey results which has employee_id, questions_id, answer as one employee to many results.

|---------------------|
| id | name | company |
|---------------------|

|-----------------------------------------|
| id | employee_id | question_id | answer |
|-----------------------------------------|

I want to Select total number of employees for each company, and total participants from each company in the survey.

I tried the following query, but it's taking too much time to execute:

SELECT employees.company as x, COUNT(DISTINCT (results.employee_id)) "Total Surveys", (SELECT COUNT(employees.id) FROM employees WHERE company = x) "Headcount"
FROM results
JOIN employees ON results.employee_id = employees.id
GROUP BY employees.company

Result

|--------------------------------|
|  x | Total Surveys | Headcount |
|--------------------------------|
| C1 |       15      |    3      |
| C2 |       10      |    5      |
|--------------------------------|

SQL Fiddle

Any recommendations?

Upvotes: 0

Views: 75

Answers (1)

Nick
Nick

Reputation: 147166

You can get the results you want by a LEFT JOIN from employees to results; then you can count both values without a subquery:

SELECT e.company, 
       COUNT(DISTINCT r.employee_id) AS `Total Surveys`,
       COUNT(DISTINCT e.id) AS `HeadCount`
FROM employees e
LEFT JOIN results r ON r.employee_id = e.id
GROUP BY e.company

Demo on SQLFiddle

Upvotes: 1

Related Questions