CODEWITHSUNDEEP
sqlsql-serversumpivot
MisterHow
MisterHow

Reputation: 23

SQL SUM multiple Rows into a single Column

I have a list of data and that data repeats a lot, plainly down to the fees. There are two types of fees; TypeA and TypeB.

I currently have:

Person Value Type
John 10 TypeA
John 10 TypeA
John 20 TypeB
John 20 TypeB
Steve 15 TypeA
Steve 15 TypeA
Steve 25 TypeB
Steve 25 TypeB

What I want is:

Person TypeA TypeB
John 20 40
Steve 30 50

Edit: I'm using SQL Server

Upvotes: 1

Views: 255

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

In SQL Server you can also write dynamic pivot. Specially it's great when you don't know all the name of the pivoted columns.

Schema and insert statements:

create table tablename(Person varchar(50), Value int, Type varchar(50));
insert into tablename values('John',10,'TypeA');
insert into tablename values('John',10,'TypeA');
insert into tablename values('John',20,'TypeB');
insert into tablename values('John',20,'TypeB');
insert into tablename values('Steve',15,'TypeA');
insert into tablename values('Steve',15,'TypeA');
insert into tablename values('Steve',25,'TypeB');
insert into tablename values('Steve',25,'TypeB');

Query:

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

select @cols=string_agg(type,',') within group (order by type)
from
(select distinct type from tablename)t

set @query = 
'SELECT Person, ' + @cols + ' from 
(
    select * from tablename    
) src
pivot 
(
    sum(value) for Type in (' + @cols + ')
) piv
order by Person'

execute(@query)

Output:

Person TypeA TypeB
John 20 40
Steve 30 50

fiddle

Upvotes: 0

Sergey
Sergey

Reputation: 5217

SELECT T.PERSON,
SUM(
    CASE
       WHEN T.TYPE='TypeA' THEN T.Value
    ELSE 0
   END
   )AS SUM_TYPE_A,
SUM(
    CASE
      WHEN T.TYPE='TypeB' THEN T.Value
      ELSE 0
    END
   )AS SUM_TYPE_B
FROM YOUR_TABLE AS T
GROUP BY T.PERSON

Upvotes: 1

Andrew
Andrew

Reputation: 8703

If there's literally only two types, you can easily do this with conditional aggregation:

select
person,
sum (case when type = 'TYPEA' then value end) as typeA,
sum (case when type = 'TypeB' then value end) as typeB
from
<table>
group by
person

Upvotes: 1

Related Questions