Using SUM with INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using SUM with INNER JOIN

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-15 : 10:10:57

Alan writes "Hi,

I have two tables Cards with an address list and Tiptree with some transactions in it, multiple transactions belonging to individual account numbers. What I want to do is add all transactions for each account number and produce a table with the address for each account against the total value

My first attempt was this

select
a.Title,
a.Firstname,
a.Lastname,
a.Address_1,
a.Address_2,
a.Address_3,
a.Address_4,
a.Address_5,
a.Postcode,
b.Account_Nb
SUM(b.Transaction_Value) AS Total_Spend
From Cards AS a INNER JOIN Tiptree AS b
ON a.Account_Nb = b.Account_Nb
group by a.Account_Nb
order by Total_Spend

This produced "Line 12: Incorrect syntax near 'b'."

???

Alan"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-15 : 10:19:49

you are actually missing a comma, but you need to do a little more. you will need to GROUP BY every column you wish to return, unless you summarize them.

I feel the best way to handle this is with a derived table that returns the total Transaction_Value per account. Once you have that, you now can JOIN from the table of all accounts to this devired table, which returns 1 value per account_nb.

This eliminates the need to GROUP BY every column you wish to return. It should offer better performance and make your query shorter and easier to read.

So, you get:

SELECT
a.Title,
a.Firstname,
a.Lastname,
a.Address_1,
a.Address_2,
a.Address_3,
a.Address_4,
a.Address_5,
a.Postcode,
a.Account_Nb, b.Total_Spend
FROM
Cards a
INNER JOIN
(SELECT Account_Nb, sum(Transaction_Value) as Total_Spend
FROM Tiptree
GROUP BY Account_Nb
) b
ON
a.Account_Nb = b.Account_Nb
order by
b.Total_Spend

Looks like more work at first, but to me that's the best way to get his information. Whenever you find yourself GROUP'ing by non-key columns in tables (like names or addresses or info like that), generally I say to use more derived tables to get the totals and then join them in to that information later.

- Jeff

Billpl
Yak Posting Veteran

71 Posts

Posted - 2003-12-17 : 00:05:12

Just curious. I would have written it this way instead. Would this method be more or less effective?

SELECT
a.Title,
a.Firstname,
a.Lastname,
a.Address_1,
a.Address_2,
a.Address_3,
a.Address_4,
a.Address_5,
a.Postcode,
a.Account_Nb,

(SELECT sum(Transaction_Value)
FROM Tiptree b
WHERE a.Account_Nb = b.Account_Nb
GROUP BY b.Account_Nb
) as Total_Spend

FROM
Cards a
ORDER BY
b.Total_Spend

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-17 : 00:37:16

quote:
Just curious. I would have written it this way instead. Would this method be more or less effective?

SELECT
a.Title,
a.Firstname,
a.Lastname,
a.Address_1,
a.Address_2,
a.Address_3,
a.Address_4,
a.Address_5,
a.Postcode,
a.Account_Nb,

(SELECT sum(Transaction_Value)
FROM Tiptree b
WHERE a.Account_Nb = b.Account_Nb
GROUP BY b.Account_Nb
) as Total_Spend

FROM
Cards a
ORDER BY
b.Total_Spend

That'll work, but you don't need the GROUP BY, and it will be much slower than Mr. Cross Join's solution because the SUM will be recalculated for each row returned from Table Cards.

Jeff's query will calculate the SUM(s) once, then join it to the result set. The overhead to calculate a recordset of grouped SUM(s) is about the same as calculating a single SUM.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources