Sum query

Question

Sum query

mrsiro

Old Hand

Points: 376
More actions
June 11, 2020 at 2:33 am

#3762168

Hello guys, i have two table like blue area and green area.
Table1: ID1 and ID2 are primary key.
Table2: ID3 is primary key.
How to get result like yellow area.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

SELECT ID2, SUM(Amount1), SUM(Amount2)
FROM   (SELECT ID2, Amount AS Amount1, 0 AS Amount2
        FROM   Table1
        UNION ALL 
        SELECT ID2, 0, Amount
        FROM   Table2) AS u
GROUP BY ID2

This reply was modified 4 years, 5 months ago by Steve Jones - SSC Editor.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

drop table if exists dbo.test_T1;
go
create table dbo.test_T1(
  ID1       varchar(8) not null,
  ID2       varchar(8) not null,
  Amount1   int not null);
go
alter table dbo.test_t1
add constraint
  pk_T1 primary key clustered (ID1, ID2);  
go

insert dbo.test_T1(ID1, ID2, Amount1) values
('A1', 'B1', 10),
('A1', 'B2', 20),
('A2', 'B1', 15);

drop table if exists dbo.test_T2;
go
create table dbo.test_T2(
  ID3       varchar(8) constraint pk_T2 primary key not null,
  ID2       varchar(8) not null,
  Amount2   int not null,
  ID1       varchar(8) not null);
go

insert dbo.test_T2(ID3, ID2, Amount2, ID1) values
('C1', 'B1', 5, 'A1'),
('C2', 'B1', 2, 'A1');

--select * from dbo.test_T1;
--select * from dbo.test_T2;

with t2_cte(ID1, ID2, Amount2) as (
    select ID1, ID2, sum(amount2) from dbo.test_T2 group by ID1, ID2)
select
 t1.ID2,
 sum(t1.Amount1) Amount1,
 sum(isnull(t2.Amount2, 0)) Amount2
from
  dbo.test_T1 t1
 left join
  T2_cte t2 on t1.ID1=t2.ID1
               and t1.ID2=t2.ID2
group by
  t1.ID2;

This reply was modified 4 years, 5 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

SELECT t1.ID2, SUM(t1.Amount1) AS Amount1, ISNULL(MAX(t2.Amount2), 0) AS Amount2
FROM dbo.table1 t1
LEFT OUTER JOIN (
    SELECT ID2, SUM(Amount2) AS Amount2
    FROM dbo.table2
    GROUP BY ID2
) AS t2 ON t2.ID2 = t1.ID2
GROUP BY t1.ID2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mrsiro Old Hand Points: 376 More actions · Answer 4

thanks guys,

Next question:

how to get result like yellow

This reply was modified 4 years, 5 months ago by mrsiro.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

And when you get that answer, what is the next question?

Now, what have you tried in order to solve your problem?

mrsiro Old Hand Points: 376 More actions · Answer 6

yes, next question is #3763756

add column price in table1, column kind in table2.

This reply was modified 4 years, 5 months ago by mrsiro.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

So did you try our solutions? And more to the point, did you try to augment any of them to fit this slant of the problem?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]