SUM and JOIN together

<< Create a complex password | Home | Excerpt from The Compression Session >>

SUM and JOIN together

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.

Feedback

# 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

10/1/2008 4:04 PM | Casual Observer

# 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

10/1/2008 4:59 PM | Michael Swart

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

10/1/2008 7:43 PM | Adam Machanic

Comments have been closed on this topic.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories