1
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;
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?

0

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;
  • Reema: this is incomplete. i don't have any idea about pivot can you please share complete the query. – dhyanandra singh Jul 28 '15 at 5:57
  • i'm using mysql database , pivot is not supported in mysql. – dhyanandra singh Jul 28 '15 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.