Return SUM that is not 0 (zero)

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

Return SUM that is not 0 (zero)

Author

Topic

FernandoLorival
Starting Member

19 Posts

Posted - 2013-10-15 : 19:24:06

Hi all,
I have this table that contains transactions for a specific client and some clients, when I total the transactions, have a zero balance.
I want to be able to filter those clients out, only show the ones that have open balances.
Here's an example:

DECLARE @T1 TABLE( CODE VARCHAR(6), ITEM INTEGER, AMOUNT INTEGER)
INSERT INTO @T1
VALUES
('ABC',4001,110),
('ABC', 4001, -110),
('ABC', 4002, -5.5),
('ABC', 7938, 5.5),
('ABC', 7938, 110),
('ABC', 4002, 5.5),
('ABC', 7938, -115.5),
('DEF', 8, -39),
('GHI', 869, -11000),
('GHI', 2093, -11917.5),
('GHI', 869, 11000),
('GHI', 870, 350),
('GHI', 871, 567.5),
('GHI', 870, -350),
('GHI', 871, -567.5),
('GHI', 2093, 567.5),
('GHI', 2093, 350),
('GHI', 2093, 11000)

If you look at the table, client ABC has a lot of transactions but if I SUM them the balance is 0, same with client GHI. The only row that show show on my query is client DEF.

I am trying to do something like this:

select *
from @T1
having Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)

but I get an error.

Thank you for your time!

singularity
Posting Yak Master

153 Posts

Posted - 2013-10-15 : 22:13:27

[code]
select *
from @T1
~~having~~ where Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:09:40

[code]
SELECT CODE, ITEM, AMOUNT
FROM
(
SELECT *,SUM(AMOUNT) OVER (PARTITION BY CODE) AS Tot
FROM @T1
)t
WHERE Tot <> 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-16 : 07:37:09

If you only want the Code:

SELECT Code FROM @t1 GROUP BY Code HAVING SUM(Amount) <> 0;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources