Combining multiple rows into one row SQL query

Question

I have an SQL table below that contains data I need to collate. What I would like to do is combine all data for the ID of 746 contained in column B so that in the Results table column R2 contains the sum of column E when column C is non zero and column R4 contains the sum of column E when column D is none zero. Both sums are then reduced by the percentage displayed in column F, Column R3 will be the sum of column C and column R5 is the sum of column D.

Source Data

+----+------+-------------+------+------------+----+
| A  |  B   |      C      |  D   |     E      | F  |
+----+------+-------------+------+------------+----+
| 78 |  746 | 27          | 0    | 592.38     | 50 |
| 78 |  746 | 27          | 0    | 592.38     | 50 |
| 78 |  746 | 0           | 52.5 | 3178.36    | 50 |
| 78 |  746 | 484.25      | 0    | 10616.8450 |    |
| 78 |  827 | 875         | 0    | 19215      | 50 |
| 78 |  827 | 125         | 0    | 2745       | 50 |
| 78 | 1078 | 63.59999847 | 0    | 1272       | 50 |
+----+------+-------------+------+------------+----+

Results

+-----+---------+--------+---------+------+
| R1  |   R2    |   R3   |   R4    |  R5  |
+-----+---------+--------+---------+------+
| 746 | 5900.80 | 511.25 | 1589.18 | 52.5 |
+-----+---------+--------+---------+------+

This script should populate the initial data

create table #Test
(   
    A int,
    B int,
    C decimal(10,2),
    D decimal(10,2),
    E decimal(10,2),
    F int
)
insert into #Test select 78, 746, 27, 0, 0, 50
insert into #Test select 78, 746, 27, 0, 592.38, 50
insert into #Test select 78, 746, 0, 52.5, 3178.36, 50
insert into #Test select 78, 746, 484.25, 0, 10616.8450, 50
insert into #Test select 78, 827, 875, 0, 19215, 50
insert into #Test select 78, 827, 125, 0, 2745, 50
insert into #Test select 78, 1078,63.60, 0, 1272, 50

As this is not something I have done a lot of in SQL server I am feeling a little flummoxed. The area where I think I need to be is subquery but am not exactly sure any help would be fantastic.

Thanks

So, you explained R3 and R5, what about the other columns? — Lamak
– Lamak, Commented Dec 30, 2013 at 16:00
Thanks for your assistance everyone but it seems I have pasted in the wrong question to begin with. I have now edited to what I wanted to say. — lostinwpf
– lostinwpf, Commented Dec 30, 2013 at 16:01
Ok, you changed the question. But now there is no explanation for columns R3 and R5. Can you post your whole question instead of parts of it? — Lamak
– Lamak, Commented Dec 30, 2013 at 16:02

Lamak · Accepted Answer · 2013-12-30 16:13:15Z

1

Ok, it seems that this is what you want:

SELECT  B AS R1,
        SUM(CASE WHEN C != 0 THEN E END)*MIN(F)/100 AS R2,
        SUM(C) AS R3,
        SUM(CASE WHEN D != 0 THEN E END)*MIN(F)/100 AS R4,
        SUM(D) AS R5
FROM #test
WHERE B = 746
GROUP BY B

Results:

╔═════╦═════════════╦════════╦═════════════╦═══════╗
║ R1  ║     R2      ║   R3   ║     R4      ║  R5   ║
╠═════╬═════════════╬════════╬═════════════╬═══════╣
║ 746 ║ 5900.805000 ║ 538.25 ║ 1589.180000 ║ 52.50 ║
╚═════╩═════════════╩════════╩═════════════╩═══════╝

The difference in the result of the column R3 is because you are not considering one of the rows.

answered Dec 30, 2013 at 16:13

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

@M.Ali I use this site, and choose the "Unicode Art" style

M.Ali Over a year ago

Will give it a go in my next answer.

Mahmoud Gamal · Accepted Answer · 2013-12-30 15:50:17Z

1

SELECT
  746 AS R1,
  SUM(c) AS R3,
  SUM(D) AS R5
FROM tablename
WHERE B = 746;

Demo

answered Dec 30, 2013 at 15:50

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

1 Comment

Cristian Lupascu Over a year ago

No problem. I had written (approximately) the same statement as yours, but you posted faster.\

Casual Pundit · Accepted Answer · 2013-12-30 15:57:31Z

0

Do you just want to sum up the unique / distinct values?

If so,

select 
    B as R1, 
    sum(distinct C) as R3,
    sum(distinct D) as R5
from #Test
group by B
where B = 746

answered Dec 30, 2013 at 15:57

Casual Pundit

2792 silver badges10 bronze badges

Comments

CRAFTY DBA · Accepted Answer · 2013-12-30 18:18:19Z

0 - I noticed a bug in the insert statement, the first line, column E should be 592.38 instead of 0.

insert into #Test select 78, 746, 27, 0, 0, 50;

A couple things to note below.

1 - Table does allow nulls. Therefore, you should use a COALESCE() to handle that case or explicit declare it as NOT NULL.

2 - Why have the percentage repeated for each row?

IE - Table is not in 3RD normal form if every row that has key B (R1) = 746 has a percentage of 50. You can create another table that has column B as the key and column F as the percentage.

I changed the code below to handle the case in which different rows have different percentages.

Other than those comments, Lamak code will work.

J

-- Drop the test table
drop table #my_test
go

-- Create the test table
create table #my_test
(   
    A int,
    B int,
    C decimal(10,2),
    D decimal(10,2),
    E decimal(10,2),
    F int
);
go

-- Add the data
insert into #my_test select 78, 746, 27, 0, 592.38, 50;
insert into #my_test select 78, 746, 27, 0, 592.38, 50;
insert into #my_test select 78, 746, 0, 52.5, 3178.36, 50;
insert into #my_test select 78, 746, 484.25, 0, 10616.8450, 50;
insert into #my_test select 78, 827, 875, 0, 19215, 50;
insert into #my_test select 78, 827, 125, 0, 2745, 50;
insert into #my_test select 78, 1078,63.60, 0, 1272, 50;
go

-- Show the data
select * from #my_test;
go

-- Create the report
SELECT  B AS R1,
        SUM(CASE 
            WHEN COALESCE(C, 0) != 0 THEN E * F / 100 END) AS R2,
        SUM(C) AS R3,
        SUM(CASE 
            WHEN COALESCE(D, 0) != 0 THEN E * F / 100 END) AS R4,
        SUM(D) AS R5
FROM #my_test
WHERE B = 746
GROUP BY B

enter image description here

Collectives™ on Stack Overflow

Combining multiple rows into one row SQL query

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related