MySQL Query Result as a specified string if int equals

Question

MySQL

I have the following query

"SELECT Status, COUNT( ticket_id) AS total FROM tickets GROUP BY Status"

Which returns

Status ¦ Total  
0      ¦   3
2      ¦   1
3      ¦   6

Is it possible to change the result to a pharse, for example if

0 = Open, 1 = Hold, 2 = Awaiting End User Response, 3 = Resolved, 4 = Closed.

So then the result of the query would be

Status                 ¦ Total  
Open                   ¦   3
Awaiting end user      ¦   1
Resolved               ¦   6

Any help would be awesome, Cheers!

See dev.mysql.com/doc/refman/8.0/en/…

Usagi Miyamoto
– Usagi Miyamoto

2019-03-28 17:05:59 +00:00
Commented Mar 28, 2019 at 17:05 — Usagi Miyamoto
– Usagi Miyamoto, Commented Mar 28, 2019 at 17:05

Yogesh Sharma · Accepted Answer · 2019-03-28 17:03:23Z

3

You need case expression :

select (case (Status) 
               when 0 then 'Open' 
               when 1 then 'Hold'
               when 2 then 'Awaiting End User Response'
               when 3 then 'Resolved'
               when 4 then 'Closed'
        end) as total, count(ticket_id) AS total 
from tickets t
group by (case (Status) 
               when 0 then 'Open' 
               when 1 then 'Hold'
               when 2 then 'Awaiting End User Response'
               when 3 then 'Resolved'
               when 4 then 'Closed'
          end);

answered Mar 28, 2019 at 17:03

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hopper Over a year ago

As Yogesh stated, you can use a case statement to replace the status integer with the phrase. Alternatively, you could create a table giving the relationship between the code and the description for the status, join to that table, and display the description instead of the code. The code tables ( Status_Code, Status_Description in this case) are really useful for ease of maintenance.

Hopper Over a year ago

@RiggsFolly thank you for your response. I moved my commentary.

Mark Over a year ago

@yogeshsharma Thanks for your assisstance it is working exactly as I need. Top Man!

Collectives™ on Stack Overflow

MySQL Query Result as a specified string if int equals

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related