I am attempting to write a query that uses multiple “CASE WHEN” to count instance for different categories.
What I have now is this:
SELECT COUNT(CASE WHEN customer_feedback = "Very Bad" THEN 1 END) /
(COUNT(*)*1.0) * 100 AS Prcnt_Very_Bad,
(CASE WHEN customer_feedback = "Bad" THEN 1 END) /
COUNT(*)*1.0)*100 AS Prcnt_Bad,
(CASE WHEN customer_feedback = "Okay" THEN 1 END) /
(COUNT(*)*1.0)*100 AS Prcnt_Okay
FROM order_t;
The first case expression runs and returns a value in the result table but the next 2 show blank columns even though the whole query runs without error. The results look like this:
Prcnt_Very_Bad | Prcnt_Bad | Prcnt_Okay |
---|---|---|
17.5 |
When I want them to look like this:
Prcnt_Very_Bad | Prcnt_Bad | Prcnt_Okay |
---|---|---|
17.5 | 20.5 | 18 |
I have tried changing the values for the THEN expression to different integers like
SELECT COUNT
(CASE
WHEN customer_feedback = "Very Bad" THEN 1
END)/(COUNT(*)*1.0)*100 AS Prcnt_Very_Bad,
(CASE
WHEN customer_feedback = "Bad" THEN 2
END)/(COUNT(*)*1.0)*100 AS Prcnt_Bad,
(CASE
WHEN customer_feedback = "Okay" THEN 3
END)/(COUNT(*)*1.0)*100 AS Prcnt_Okay
FROM order_t;
As well as doing stacked subqueries with the CASE WHEN expression but I still get the same blank results as above.
Flora97 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4