CODEWITHSUNDEEP
sqlsql-server
gaffcz
gaffcz

Reputation: 3479

how to sum listed values in column

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

Upvotes: 0

Views: 95

Answers (5)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6073

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

Upvotes: 2

juergen d
juergen d

Reputation: 204756

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

Upvotes: 3

TechDo
TechDo

Reputation: 18629

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')

Upvotes: 1

DNNX
DNNX

Reputation: 6255

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)

Upvotes: 0

Related Questions