CODEWITHSUNDEEP
mysql
Kisaragi
Kisaragi

Reputation: 2218

Mysql count with case when statement

Consider: 

SELECT(count(c.id),
    case when(count(c.id) = 0)
             then 'loser'
         when(count(c.id) BETWEEN 1 AND 4)
             then 'almostaloser'
         when(count(c.id) >= 5)
             then 'notaloser'
    end as status,
   ...

When all is said and done, the query as a whole produces a set of results that look similar to this:

   Count  | status
  --------|-------------
     2    | almostaloser   //total count is between 2 and 4
  --------|-------------
     0    | loser          // loser because total count = 0
  --------|-------------
     3    | almostaloser  //again, total count between 2 and 4
  --------|-------------

What I would like to achieve:

a method to reatain the information from the above table, but add a third column that will give a total count of each status, something like 

   select count(c.id)
     case when(count(c.id) = 0 )
        then loser as status AND count how many  of the total count does this apply to

results would look similar to:

   Count  | status      |total_of each status |
  --------|-------------|---------------------|
     2    | almostaloser|   2                 |   
  --------|-------------|---------------------|
     0    | loser       |   1                 |  
  --------|-------------|---------------------|
     3    | almostaloser|   2                 |
  --------|-------------|----------------------

I've been told this could be achieved using a derived table, but i've not yet been able to get them both, only one or the other.

Upvotes: 0

Views: 99

Answers (1)

Rimas
Rimas

Reputation: 6024

This can be achieved with this query (you must place your original query as subquery in two places):

SELECT t1.*, t2.total_of_each_status
  FROM (
    -- put here your query --
  ) t1
  INNER JOIN (
    SELECT status, count(*) AS total_of_each_status
      FROM (
        -- put here your query --
      ) t2
      GROUP BY status
  ) t2 ON t2.status = t1.status

Upvotes: 1

Related Questions