Group By Having Multiple Row SUM

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 2008 Forums

Transact-SQL (2008)

Group By Having Multiple Row SUM

Author

Topic

rickincanada
Starting Member

18 Posts

Posted - 2011-08-22 : 16:56:52

Hi there, I'm hung up on what should be a fairly simple SELECT however I cannot resolve my issue for the life of me. I'm hoping someone here can point me in the direction of a solution.

Here's a shortened simplified version of my SELECT:

SELECT vehicle_id
, amount
, account
FROM vehicle
INNER JOIN accounting ON vehicle_id = accounting_vehicle_id
WHERE account IN ('100', '101', '102', '103')
GROUP BY vehicle_id, amount, account

This gets me back a number of vehicle rows where the SUM of the amount rows = 0. Here's an example:

vehicle_id | amount | account
1500 | 1000.00 | 101
1500 | -500.00 | 102
1500 | -500.00 | 103

I want to remove these rows from this SELECT however I cannot figure this out!! Please help!!

Thanks,
Rick

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-22 : 17:04:26

you are not using SUM anywhere in your query

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-22 : 17:07:14

what you might be looking for is


SELECT vehicle_id
, SUM(amount)
, account
FROM vehicle
INNER JOIN accounting ON vehicle_id = accounting_vehicle_id
WHERE account IN ('100', '101', '102', '103')
GROUP BY vehicle_id, amount, account
having SUM(amount) > 0

If you don't have the passion to help people, you have no passion

rickincanada
Starting Member

18 Posts

Posted - 2011-08-22 : 17:20:59

I've tried the HAVING method and it didn't work. I think I'm closer as I've added a sub query to SUM the amount by vehicle "AS 'Balance'" however I can't figure out how to add a WHERE [Balance] <> 0. It keeps telling me "Invalid column name 'Balance'".

Thanks for your help.

rickincanada
Starting Member

18 Posts

Posted - 2011-08-22 : 17:23:30

Never mind - got it - moved the sub query into the WHERE clause and we're up and running. Thanks for making me take a different look at this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources