Query Related to SQL for count records

Question

I have a table for feedback in which i am getting feed backs from customers. he table looks like this

ID  Service      Food      Behavior  Environment   OverallExprience  Flight    Date

1   Excellent   VeryGood    Good     Bad            Poor             EK0603  2018-03-29 
2   Excellent   Good      VeryGood   Excellent      Poor             EK0603  2018-03-29

All I want from employee is to search the date (from ---> to) and get the brief report which includes

Number of excellent
Number of very good
Number of good
Number of bad
poor

for all the given options. Like in the given Data if Employe selected the date range of 2018-03-29 to 2018-03-29

So he can see the records like this

              Excellent        Very Good    Good     Bad     poor
Service         2                 0           0       0        0

Food            0                 1           1       0        0

Behavior        0                 1           1       0        0

Environment     1                 0           0       1        0

Stuck here need help . will be appreciable if the answer is in LINQ query

What have you tried so far? Start simple and then expand on that. — SS_DBA, Mar 30 '18 at 18:46
select count(*)from Feedbacks group by OverAllExperience doing like this but not getting the desired output in one query — Faizan Naeem, Mar 30 '18 at 18:50
@FaizanNaeem That doesn't look like LINQ? Perhaps my [SQL to LINQ recipe](https://stackoverflow.com/a/48956709/2557128) would help you? — NetMage, Mar 30 '18 at 19:06
I don't know whats wrong in the question ? If you don't know the answer , vote it down lol — Faizan Naeem, Mar 30 '18 at 19:09
Have you tried `SUM(Case)` expression? Since you're only dealing with a specific set of columns you want reported on, I think this would be your best solution to start with. — SS_DBA, Mar 30 '18 at 19:59

Alex Zen · Accepted Answer · 2018-03-30T22:48:20.810

Because you want to transpose columns to rows, I guess you need a few UNIONs. You would probably need a different query for each "category", and for each category you should count how many votes do they get for each option.

Please try this query out, it should do it:

SELECT MAX('Service') AS Category,
       COUNT(CASE WHEN t.Servive = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.Servive = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.Servive = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.Servive = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.Servive = 'Poor' THEN 1 END) AS Poor
  FROM table1 t
 WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Food') AS Category,
       COUNT(CASE WHEN t.Food = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.Food = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.Food = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.Food = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.Food = 'Poor' THEN 1 END) AS Poor
  FROM table1 t
 WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Behavior') AS Category,
       COUNT(CASE WHEN t.Behavior = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.Behavior = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.Behavior = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.Behavior = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.Behavior = 'Poor' THEN 1 END) AS Poor
  FROM table1 t
 WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29'
UNION
SELECT MAX('Environment') AS Category,
       COUNT(CASE WHEN t.Environment = 'Excellent' THEN 1 END) AS Excellent,
       COUNT(CASE WHEN t.Environment = 'VeryGood' THEN 1 END) AS Very_Good,
       COUNT(CASE WHEN t.Environment = 'Good' THEN 1 END) AS Good,
       COUNT(CASE WHEN t.Environment = 'Bad' THEN 1 END) AS Bad,
       COUNT(CASE WHEN t.Environment = 'Poor' THEN 1 END) AS Poor
  FROM table1 t
 WHERE t.Date BETWEEN '2018-03-29' AND '2018-03-29'

Thanks (Y) . got the desired output by executing this one – Faizan Naeem Mar 31 '18 at 18:30 — Faizan Naeem, Mar 31 '18 at 18:30

Query Related to SQL for count records

1 Answers1

Linked