CODEWITHSUNDEEP
sqlt-sqlgrouping
Milkman
Milkman

Reputation: 11

combining grouped rows

   select *
     into #HoldingTable
     FROM
(Select A,
        B,
    sum(cast (C as money)) as C,
    count(D) as D
        FROM Table1 T1
        WHERE (stuff)
                group by A,B

union all
   Select A,
          B,
   sum(cast (C as money)) as C, 
   count(D) as D
        FROM Table2 T2
        WHERE (stuff)
              group by A,B
) as abc

When getting the results if T1 and T2 have results with the same A,B it'll list it in 2 different rows I want to combine these rows and sum C and D When A and B are the same.

select * from HoldingTable

aaa,bbb,10,4
aaa,bbb,5,3

Would like it to be

aaa,bbb,15,7

Is there a way to do that?

Upvotes: 0

Views: 30

Answers (1)

Bee_Riii
Bee_Riii

Reputation: 1039

This is simpler to read and should do the same thing as what you are attempting.

SELECT A, B, SUM(C) AS C, Count(D) AS D
INTO #HoldingTable
FROM (
    SELECT A, B, cast(C AS MONEY) AS C, D
    FROM Table1 T1
    WHERE (stuff)
    
    UNION ALL
    
    SELECT A, B, cast(C AS MONEY) AS C, D
    FROM Table1 T1
    WHERE (stuff)
    ) AS abc
GROUP BY A, B

You could do the aggregation in both the subquery and outer query as below but that just makes it harder to read IMO.

SELECT A, B, Sum(C) AS C, Sum(D) AS D
INTO #HoldingTable
FROM (
    SELECT A, B, sum(cast(C AS MONEY)) AS C, count(D) AS D
    FROM Table1 T1
    WHERE (stuff)
    GROUP BY A, B
    
    UNION ALL
    
    SELECT A, B, sum(cast(C AS MONEY)) AS C, count(D) AS D
    FROM Table2 T2
    WHERE (stuff)
    GROUP BY A, B
    ) AS abc
GROUP BY A, B

Upvotes: 1

Related Questions