Question 113024 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL how to group by specific value?

Let’s say I have a table like this:

     id     |   amount
 -------------------------
     1      |    10 
     1      |    10   
     2      |    20   
     2      |    10   
     3      |    20   
     3      |    10   
     3      |    10   
     4      |    10

Let’s say id 1 and 2 are special, I want to group them together and name it as special. Ids that are other than 1 and 2 should also not be grouped together. Lastly, I want to sum the amount. So, how to I get the result like this:

    type     |   total_amount
 -------------------------------
   special   |      50 
   3         |      40   
   4         |      10
Tags:

3

Answers


  1. 0

    You could use a case when to transform id‘s values into the ones you are after, and then apply a sum() function to get the aggregate:

    select
  case 
    when id in (1, 2) then 'special'
    else cast(id as char)
  end as type,
  sum(amount) as total_amount
from <your_table>
group by 1
    Login or Signup to reply.
  2. 0

    please try this

    SELECT 
    CASE
        WHEN id IN (1, 2) THEN 'special'
        ELSE CAST(id AS CHAR)
    END AS type, 
    SUM(amount) AS total_amount
FROM __table__
GROUP BY id
    Login or Signup to reply.
  3. 0

    You could also see separately each ID of special with the below code :

    select case 
        when id in (1, 2) then 'Special'
        else id
       end type,
       id,
       sum(amount) total_amount
  from table
 group by 1,2
    type id amount
    special 1 20
    special 2 30
    3 3 40
    4 4 10
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search