Peter Larsson Blog

Patron Saint of Lost Yaks

SUM and JOIN together

by Peter Larsson in  , , ,

DECLARE    @a TABLE (pk INT)
DECLARE    @b TABLE (fk INT, i INT)
DECLARE    @c TABLE (fk INT, j INT)

INSERT    @a
SELECT    1 UNION ALL
SELECT    2 UNION ALL
SELECT    3

INSERT    @b
SELECT    1, 1 UNION ALL
SELECT    1, 3 UNION ALL
SELECT    2, 4 UNION ALL
SELECT    2, 8 UNION ALL
SELECT    2, 10 UNION ALL
SELECT    3, 1

INSERT    @c
SELECT    1, 11 UNION ALL
SELECT    1, 13 UNION ALL
SELECT    2, 14 UNION ALL
SELECT    2, 18 UNION ALL
SELECT    2, 60 UNION ALL
SELECT    3, 11

-- Wrong way
SELECT        a.pk,
        SUM(b.i) AS SumAct,
        SUM(c.j) AS SumDebt
FROM        @a AS a
LEFT JOIN    @b AS b ON b.fk = a.pk
LEFT JOIN    @c AS c ON c.fk = a.pk
GROUP BY    a.pk

-- Right way
SELECT  a.pk, 
        b.i AS SumAct, 
        c.j AS SumDebt
FROM        @a as a
LEFT JOIN    (
            SELECT        fk,
                    SUM(i) AS i
            FROM        @b
            GROUP BY    fk
        ) AS b ON b.fk = a.pk
LEFT JOIN    (
            SELECT        fk,
                    SUM(j) AS j
            FROM        @c
            GROUP BY    fk
        ) AS c ON c.fk = a.pk

Yes, of course there is no need to distinct the primary table.
A lapsus from my side.

Legacy Comments
Casual Observer
2008-10-01		 re: SUM and JOIN together
Why not...?

SELECT a.pk,
b.i AS SumAct,
c.j AS SumDebt
FROM (
SELECT pk
FROM @a
GROUP BY pk
)AS a
LEFT JOIN (
SELECT fk,
SUM(i) AS i
FROM @b
GROUP BY fk
) AS b ON b.fk = a.pk
LEFT JOIN (
SELECT fk,
SUM(j) AS j
FROM @c
GROUP BY fk
) AS c ON c.fk = a.pk
Michael Swart
2008-10-01		 re: SUM and JOIN together
Using CTE's:

WITH SUMMEDI(fk, SumAct) AS
(
SELECT fk, SUM(i) as SumAct FROM @b GROUP BY fk
),
SUMMEDJ(fk, SumDebt) AS
(
SELECT fk, SUM(j) as SumDebt FROM @c GROUP BY fk
)
SELECT a.pk, b.SumAct, c.SumDebt
FROM @a as a
LEFT JOIN SUMMEDI as b
ON b.fk = a.pk
LEFT JOIN SUMMEDJ as c
ON c.fk = a.pk
Adam Machanic
2008-10-01		 re: SUM and JOIN together
I like what Michael Swart did, removing the @a table from the derived table -- if it's really the "PK" that should not be necessary. But it's still not the most readable thing in the world with the CTEs. If you're only getting one column from each of the related tables, subselects might be the way to go here from a readability point of view.