February 26, 2007 at 7:52 pm
I am too tired and in a bit of a rush, and I can't remember how to get this done
I have a table similar to a general ledger where you have a positive amount and a negative amount.
I need to quickly select a list of transactions that doesn't total zero.
For example;
Trans # Amount
======= ======
ABC 50
ZYX 25
ABC -50
I need to get:
ZYX 50
Cheers
February 26, 2007 at 8:43 pm
Try this:
SET NOCOUNT ON
-- Drop tables if alraedy exist
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
-- Creat table Table1 and populate data
Create Table [dbo].Table1 ([id] int identity(1,1),[Name] Varchar(20) NULL,[Sale Amount] Money NULL)
Insert Into [dbo].Table1
SELECT 'ABC', 50 UNION ALL
SELECT 'ZYX', 25 UNION ALL
SELECT 'ABC', -50 UNION ALL
SELECT 'ZYX', -25 UNION ALL
SELECT 'DEF', 25 UNION ALL
SELECT 'DEF', -25 UNION ALL
SELECT 'ZYX', 50
SELECT [Name], SUM([Sale Amount]) As [Total Sales]
FROM [dbo].[Table1]
GROUP BY [Name]
HAVING SUM([Sale Amount])>0
February 27, 2007 at 12:57 am
Yes, that's it... just with HAVING SUM([Sale Amount])<>0, because you want to see all not equal to zero, not only those bigger than zero.
There could be a question how do you want to display transactions where there are 2 (or more) rows, but the sum is not zero - if you just want the transaction number and the balance, you're OK with this code.
On the other hand, if you want to display all rows for such transactions, you have to add a few lines to the code (derived table Q works as a filter here):
SELECT Table1.[Name], Table1.[Sale Amount]
FROM Table1
JOIN
(SELECT t1.[Name], SUM(t1.[Sale Amount]) As [Total Sales]
FROM Table1 t1
GROUP BY t1.[Name]
HAVING SUM(t1.[Sale Amount])<>0) as Q ON Q.[Name]=Table1.Name
ORDER BY Table1.[Name]
BTW, I suppose that the requirement to get ZYX=50 is a typo and it should have been 25.
February 27, 2007 at 9:11 am
Thank you - worked like a charm.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply