Mysql – group result of multiple table to get unique result after count operation

countgroup byjoin;mysql-5.6

survey_categories
+---------------+-------------------+
| survey_cat_id | survey_cat_status |
+---------------+-------------------+
|             1 |                 1 |
|             2 |                 1 |
|             3 |                 1 |
|             4 |                 1 |
+---------------+-------------------+

survey_question
+---------+---------------+------+
| ques_id | survey_cat_id | data |
+---------+---------------+------+
|       1 |          1    | c    |
|       2 |          1    | c    |
|       3 |          1    | t    |
|       4 |          2    | c    |
|       5 |          2    | c    |
|       6 |          3    | c    |
|       7 |          3    | t    |  
|       8 |          3    | c    |
|       9 |          4    | t    |
|      10 |          4    | t    |    
+---------+---------------+------+

survey_details
+-----------+---------+----------+---------------+
| survey_id | ques_id | store_id | answer        |
+-----------+---------+----------+---------------+
|         1 |       1 |     102  |excellent      |
|         1 |       2 |     102  |very Good      |
|         1 |       3 |     102  |xxxxxxxxxxxxx  |
|         1 |       4 |     102  |very Good      |
|         1 |       5 |     102  |poor           |
|         1 |       6 |     102  |excellent      |
|         1 |       7 |     102  |xxxxxxxxxxxxx  |
|         2 |       1 |     102  |excellent      |
|         2 |       2 |     102  |very Good      |
|         2 |       3 |     103  |xxxxxxxxxxxxx  |
|         2 |       4 |     103  |very Good      |
|         2 |       5 |     103  |poor           |
|         2 |       6 |     103  |excellent      |
|         2 |       7 |     103  |xxxxxxxxxxxxx  |
+-----------+---------+----------+---------------+

I need a solution in form of query to get following result
output:

+---------------+----------+----------+---------------+----------+----------+---------------+
| survey_cat_id | store_id |excellent | Very Good     |Good      |Average   |Poor           |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 1             | 102      |2         | 2             |0         |0         |0              |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 2             | 102      |0         | 1             |0         |0         |1              |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 3             | 102      |1         | 0             |0         |1         |0              |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 1             | 103      |0         | 0             |0         |0         |0              |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 2             | 103      |0         | 1             |0         |0         |1              |
+---------------+----------+----------+---------------+----------+----------+---------------+
| 3             | 103      |0         | 1             |0         |0         |0              |
+---------------+----------+----------+---------------+----------+----------+---------------+

to achieve this i have written an sql query but it is not providing expected result: 

select survey_categories.survey_cat_id,
   survey_questions.ques_id,
   survey_Details.store_id,
   count( Answer = 1 ) AS Excellent,
   count( Answer = 2 ) AS Very_Good, 
   count( Answer = 3 ) AS Good, 
   count( Answer = 4 ) AS Average,
   count( Answer = 5 ) AS Poor

from survey_categories 

INNER JOIN survey_questions ON survey_categories.survey_cat_id = survey_questions.ques_title

INNER JOIN survey_details ON survey_questions.ques_id = survey_details.ques_id

group by survey_categories.survey_cat_id order by ques_id,answer;

Best Answer

I've modified your query and I used CASE statement to COUNT:

Query:

select 
    survey_categories.survey_cat_id,
    survey_question.ques_id,
    survey_details.store_id,
    count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, 
    count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good, 
    '' AS Average,
    count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor 
from survey_categories 
INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
group by survey_categories.survey_cat_id,survey_question.ques_id
order by ques_id,answer;

Test:

mysql> select 
    -> survey_categories.survey_cat_id,
    -> survey_question.ques_id,
    -> survey_details.store_id,
    -> count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    -> count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, 
    -> count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good, 
    -> '' AS Average,
    -> count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor 
    -> from survey_categories 
    -> INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
    -> INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
    -> group by survey_categories.survey_cat_id,survey_question.ques_id
    -> order by ques_id,answer;
+---------------+---------+----------+-----------+-----------+------+---------+------+
| survey_cat_id | ques_id | store_id | Excellent | Very_Good | Good | Average | Poor |
+---------------+---------+----------+-----------+-----------+------+---------+------+
|             1 |       1 |      102 |         2 |         0 |    0 |         |    0 |
|             1 |       2 |      102 |         0 |         2 |    0 |         |    0 |
|             1 |       3 |      102 |         0 |         0 |    0 |         |    0 |
|             2 |       4 |      102 |         0 |         2 |    0 |         |    0 |
|             2 |       5 |      102 |         0 |         0 |    0 |         |    2 |
|             3 |       6 |      102 |         2 |         0 |    0 |         |    0 |
|             3 |       7 |      102 |         0 |         0 |    0 |         |    0 |
+---------------+---------+----------+-----------+-----------+------+---------+------+
7 rows in set (0.00 sec)

mysql>

By the way, for AVG, what is your expected calculation against which fields?

Related Question