Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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.

Print | posted on Wednesday, October 01, 2008 3:48 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

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

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

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

Powered by:
Powered By Subtext Powered By ASP.NET