CODEWITHSUNDEEP
mysqlgroup-by
Kevin Rave
Kevin Rave

Reputation: 14466

Multiple rows into one field on GROUPED results Mysql

I have a query results like this. 

    status      count
    -----------------
    OTHER       5
    ATTENDED    74

I want result like this:

    OTHERS        ATTENDED
     5                74

Note this is already a grouped result. So I am thinking, Group_Concat might not work.

Query:

   SELECT CASE Rstat WHEN 1 THEN 'ATTENDED' 
                            WHEN 2 THEN 'TENTATIVE' 
                            WHEN 3 THEN 'REJECTED' 
                            WHEN 4 THEN 'OUTSTANDING' 
                            WHEN 6 THEN 'ACCEPTED' ELSE 'OTHER' END AS 'status',      
                            COUNT(Rstat) AS 'count' 
       FROM  `eStatus` 
       GROUP BY RSTAT

Any other ideas?

Upvotes: 1

Views: 192

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

SELECT SUM(CASE WHEN Rstat = 1 THEN 1 ELSE 0 END) AS ATTENDED,
       SUM(CASE WHEN Rstat = 2 THEN 1 ELSE 0 END) AS TENTATIVE,
       SUM(CASE WHEN Rstat = 3 THEN 1 ELSE 0 END) AS REJECTED,
       SUM(CASE WHEN Rstat = 4 THEN 1 ELSE 0 END) AS OUTSTANDING,
       SUM(CASE WHEN Rstat = 6 THEN 1 ELSE 0 END) AS ACCEPTED,
       SUM(CASE WHEN Rstat NOT IN (1,2,3,4,6) THEN 1 ELSE 0 END) AS OTHER
    FROM eStatus

Upvotes: 1

Related Questions