Query Related to SQL for count records [closed]

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

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'