replace item names with new group name and sum the values of the group

I have the following from another query

name | count
a    | 1000
b    | 100
c    | 100
d    | 100
x    | 100
y    | 100
z    | 100

I need to create the final results where "a" is left unchanged; names matching 'b', 'c', 'd' are grouped as "group_B" with its new value as the sum of the three rows; all other names are grouped as "others" with its new value as the sum of all other names. Thank you!

>Solution :

I would recommend changing the first query to append another column. If your original query is something like this:

SELECT things
FROM table

Change it to this:

SELECT 
things,
CASE 
    WHEN name='a' THEN NULL
    WHEN name IN ('b','c','d') THEN 'group_B'
    ELSE 'others'
END AS grouping

It should then be pretty easy to group your results how you like.