CODEWITHSUNDEEP
sqlsql-servergroup-by
steven
steven

Reputation: 393

SQL GROUP BY different values of one coloumn

on my SQL Server I got the following table - simplified:

year    month    category    value
2009    9        1           10
2009    9        2           20
2009    9        3           40
...     ...      ...         ...

Now I want to group by year, month and category (1+2, 3, ...), so that my result looks like this:

year    month    category    value
2009    9        1+2         30
2009    9        3           40
...     ...      ...         ...

It is not possible to make any changes on the table. Furthermore it is important that the SQL runs fast...

How do make this GROUP BY? I couldn't find anything helpful yet...

Thanks for your help...

Upvotes: 5

Views: 24602

Answers (2)

Ali
Ali

Reputation: 484

I found this more easily to understand by myself, and also can be easily implemented in sqlite

SELECT
    t.year,
    t.month,
    (CASE WHEN t.category = 1 OR t.category = 2 THEN '1+2' ELSE t.category END) as category,
    sum(value) AS value
FROM table_name AS t 
GROUP BY (CASE WHEN t.category = 1 OR t.t.category = 2 THEN '1+2' ELSE t.category END)

Upvotes: 7

roman
roman

Reputation: 117345

with cte as (
   select
       year, month, value,
       case
           when category in (1, 2) then '1+2'
           else cast(category as varchar(max))
       end as category
   from Table1
)
select
    year, month, category, sum(value)
from cte
group by year, month, category

or

select
    t.year, t.month, isnull(c.category, t.category), sum(value)
from Table1 as t
    left outer join (values
        (1, '1+2'),
        (2, '1+2')
    ) as c(id, category) on c.id = t.category
group by t.year, t.month, isnull(c.category, t.category)

sql fiddle demo

Upvotes: 9

Related Questions