Sum query

  • 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.

  • 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

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

  • 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 2 years, 3 months ago by  Steve Collins.

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

  •  

    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) A socialist is someone who give you the shirt off *someone else's* back.

  • thanks guys,

    Next question:

    how to get result like yellow

    • This reply was modified 2 years, 3 months ago by  mrsiro.
  • And when you get that answer, what is the next question?

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

     

  • yes, next question is #3763756

    add column price in table1, column kind in table2.

    • This reply was modified 2 years, 3 months ago by  mrsiro.
  • 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]

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

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