Query Related to SQL for count records [closed]

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

Answer 1

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'