Duplicate Rows Returned... - SQL Server Forums

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

Transact-SQL (2005)

Duplicate Rows Returned...

Author

Topic

smithygreg
Starting Member

37 Posts

Posted - 2007-09-05 : 17:52:22

Hi. I am trying to run an aggregate query where I am summing up the values in one table based on criteria contained in another table. The table that has the criteria has two records for each unique record in the summing table, so I am getting numbers that are 2X what they should be.
Is there a type of join that will only return the rows from one table while using the other tables as criteria?
Here is my example...


SELECT     Service.Name,
SUM(TransactionComparison.Calculation1TotalRate) AS Calc1, 
SUM(TransactionComparison.Calculation2TotalRate) AS Calc2,
FROM         TransactionComparison
INNER JOIN TransactionCalculation ON TransactionComparison.TransactionID = TransactionCalculation.TransactionID 
INNER JOIN Service ON TransactionCalculation.ServiceID = Service.ID
WHERE					(TransactionComparison.ComparisonID = @ComparisonID) 
						AND (TransactionCalculation.Status IN (0, 20))
						AND (TransactionCalculation.ServiceID IS NOT NULL)						
GROUP BY Service.Name

The TransactionCalculation Table has two records for each TransactionID in the TransactionComparison table so my numbers are twice what I am looking for...
Any ideas?
Thanks a heap!
-Greg

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-05 : 22:15:24

SELECT c.Name,
SUM(a.Calculation1TotalRate) AS Calc1,
SUM(a.Calculation2TotalRate) AS Calc2
FROM
TransactionComparison a
INNER JOIN
(
select Distinct aa.TransactionID
,aa.ServiceID
,aa.Status
from TransactionCalculation aa
) b
ON a.TransactionID = b.TransactionID
INNER JOIN
[Service] c
ON b.ServiceID = c.ID
WHERE
a.ComparisonID = @ComparisonID
AND b.Status IN (0, 20)
GROUP BY c.Name

smithygreg
Starting Member

37 Posts

Posted - 2007-09-07 : 19:05:07

Thanks Vinnie..that did the trick!
-Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources