Group and join with two dependents

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

General SQL Server Forums

New to SQL Server Programming

Group and join with two dependents

Author

Topic

Mikael
Starting Member

1 Post

Posted - 2012-09-05 : 13:23:44

Hi

I have two tables

Transactions
ID | Amount | year
1 | 100 | 2012
1 | -100 | 2011
2 | 100 | 2012
3 | 100 | 2012
3 | -50 | 2011

Invoice
ID | Number | year
1 | ABC123 | 2011
2 | DEF456 | 2012
3 | GHI789 | 2011

I need to write a query that looks through the transactionstable and shows all ID whose total amount isnt zero. ID in this case isnt a key and can be reused for all years. That's why in my query I want to display the invoice that correspond to the ID and the smallest year for that ID in transactions.

This is what I've tried:
SELECT ID, sum(amount) FROM transactions
LEFT JOIN
ON transactions.id = invoice.id
AND transactions.year = invoice.year
GROUP by transactions.id
HAVING (sum(transactions.id)<>0)

In this case I get this result:
1 100 NULL
1 -100 ABC123
2 100 DEF456
3 100 NULL
3 -50 GHI789

This is what I want:
2 100 DEF4569
3 50 GHI789

Anyone got any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-05 : 14:25:34

May be more like this?

SELECT
	t.Id,
	SUM(t.AMOUNT),
	i.Number
FROM
	Transactions t
	INNER JOIN Invoice i ON i.ID = t.ID
GROUP BY
	t.Id,
	i.Number
HAVING
	SUM(t.AMOUNT) <> 0;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources