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 

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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