0

A have for example following data:

a 10
b  5
c 15 
d  2
a  3
b  6 
c  8 
d 10

How to sum a and b together and c and d to get something like this?

ab 24
cd 35
gaffcz
  • 3,469
  • 14
  • 68
  • 108

5 Answers5

3
select sum(case when col1 in ('a','b') then col2 end) as ab_sum,
       sum(case when col1 in ('c','d') then col2 end) as cd_sum
from your_table
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • this (2 columns) does not match the expected result (on 2 rows) - most answers are trying to match that result - if you wanted 2 columns you should display it as such – Paul Maxwell Aug 13 '14 at 12:46
  • 1
    The only answer which doesn't do what is asked in the question is accepted :) – DNNX Aug 13 '14 at 12:46
  • Sometimes there is an easier solution to a problem and OP might like it. – juergen d Aug 13 '14 at 13:06
2

this could also help you,

DECLARE @TAB TABLE(NAME VARCHAR(1), MARK INT)
INSERT INTO @TAB VALUES 
('A',10),
('B', 5),
('C',15),
('D', 2),
('A', 3),
('B', 6),
('C', 8),
('D',10)

SELECT  NAME,SUM(MARK) 
FROM    (
        SELECT  CASE WHEN NAME IN ('A','B') THEN 'AB' WHEN NAME IN ('C','D') THEN 'CD' END NAME,
                MARK 
        FROM @TAB A 
        ) LU
GROUP   BY NAME
Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
1

Try:

select 
    'ab' col1, 
    SUM(col2) col2 
from tbl 
where col1 in ('a', 'b')
union
select 
    'cd', 
    SUM(col2) 
from tbl 
where col1 in ('c', 'd')
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • He's just writing what we wants without showing us what he tried or searched. I just find it not StackExchange fair to give him answers like this. But in fact, that's none of my business – Kabulan0lak Aug 13 '14 at 13:10
1

looks like a good opportunity for between to me:

| CODE_RANGE | RANGE_VALUE |
|------------|-------------|
|         ab |          24 |
|         cd |          35 |

SELECT
      CASE
            WHEN code BETWEEN 'a' AND 'b' THEN 'ab'
            WHEN code BETWEEN 'c' AND 'd' THEN 'cd' END AS code_range
    , SUM(value)                                        AS range_value
FROM Table1
GROUP BY
      CASE
            WHEN code BETWEEN 'a' AND 'b' THEN 'ab'
            WHEN code BETWEEN 'c' AND 'd' THEN 'cd' END

see: http://sqlfiddle.com/#!3/a6b7e3/1

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0
SELECT
  (CASE col1 
  WHEN 'a' THEN 'ab'
  WHEN 'b' THEN 'ab'
  WHEN 'c' THEN 'cd'
  WHEN 'd' THEN 'cd' 
  END) x,
  SUM(col2) y
FROM tbl
GROUP BY (CASE col1 
  WHEN 'a' THEN 'ab'
  WHEN 'b' THEN 'ab'
  WHEN 'c' THEN 'cd'
  WHEN 'd' THEN 'cd' 
  END)
DNNX
  • 6,215
  • 2
  • 27
  • 33