SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return SUM that is not 0 (zero)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

FernandoLorival
Starting Member

Canada
19 Posts

Posted - 10/15/2013 :  19:24:06  Show Profile  Reply with Quote
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

151 Posts

Posted - 10/15/2013 :  22:13:27  Show Profile  Reply with Quote

select *
from @T1
having where Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/16/2013 :  02:09:40  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/16/2013 :  07:37:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000