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

Question

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;

Answer 1

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?

Answer 2

you can also achieve this result by using Pivot as below.

;with cte as ( 
 SELECT sq.survey_cat_id, sd.Store_id,answer 
 FROM survey_details sd 
 INNER JOIN survey_question sq ON sd.ques_id = sq.ques_id 
 ) 
 Select * from cte 
 PIVOT( 
 COUNT(answer) 
 FOR answer 
 IN ([excellent], [very Good],[Good],[Average],[Poor]) 
 ) 
 AS resultset;

• dhyanandra singh: Reema: this is incomplete. i don't have any idea about pivot can you please share complete the query.
• dhyanandra singh: i'm using mysql database , pivot is not supported in mysql.