Additional JOIN altering values

Can anyone see why I would get different GrossSales values by adding the
JOIN on LaborJobCosts?
****************************************
******
This gives the correct GrossSales values:
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales
FROM Sales S
RIGHT JOIN SaleCodes SC
ON SC.ItemSaleCode = S.ItemSaleCode
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (correct):
ItemSaleCode GrossSales
C 373807.08
D 39213.52
P 113303.00
R 204072.92
S 119939.00
W 506886.13
****************************************
******
Now if I add a JOIN for the LaborCosts table, I get incorrect values for my
GrossSales (but the LaborCosts are correct!):
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales,
ISNULL(SUM(ISNULL(LC.LaborCost,0)), 0) AS LaborCosts
FROM Sales S
RIGHT JOIN SaleCodes SC ON SC.ItemSaleCode = S.ItemSaleCode
LEFT OUTER JOIN LaborJobCosts LC ON LC.SalesID = S.SalesID
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (inflated and incorrect):
ItemSaleCode GrossSales
C 936678.78
D 29213.52
P 300171.00
R 264072.84
S 207079.00
W 529586.13
****************************************
******
CREATE TABLE [dbo].[Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
[CompletedDate] [smalldatetime] NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SaleCodes] (
[SaleCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LaborJobCosts] (
[LaborCostsID] [int] IDENTITY (1, 1) NOT NULL ,
[SalesID] [int] NULL ,
[LaborCost] [money] NULL
) ON [PRIMARY]
GOIt looks like SalesId isn't unique in the LaborJobCosts table. Try the
following query. Notice that RIGHT JOIN is redundant in your original - the
WHERE clause turns it into an INNER JOIN anyway. I've also taken out the
ISNULLs from inside the SUM functions - they don't achieve anything except
possibly slow things down.
It helps if you include keys and constraints with your DDL and also post
INSERT statements for some sample data. What you did post tells us that the
LaborJobCosts doesn't have any key other than the IDENTITY column. That's a
potentially serious design flaw.
SELECT SC.itemsalecode,
ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
ISNULL(SUM(LC.laborcost),0) AS laborcosts
FROM Sales S
JOIN SaleCodes SC
ON SC.itemsalecode = S.itemsalecode
LEFT JOIN
(SELECT salesid, SUM(laborcost) AS laborcost
FROM LaborJobCosts
GROUP BY salesid) AS LC
ON S.salesid = LC.salesid
WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
GROUP BY SC.itemsalecode
David Portas
SQL Server MVP
--|||hi
you might be getting a lot rows from the query and they are getting
hidden because of the SUM and GROUP BY,
please try to remove the SUM and GROUP BY and see the duplicate rows.
once the duplicate rows are eliminated then u can see the expected
results again
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||David Portas wrote:
> I've also taken out the ISNULLs from inside the SUM functions
> - they don't achieve anything except possibly slow things down.
They MAY achieve something: if there are NULL-s in that column, the
ISNULL from inside the SUM prevents the "Warning: Null value is
eliminated by an aggregate or other SET operation.". For example:
CREATE TABLE Test (
ID int primary key,
Value int NULL
)
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
SET NOCOUNT ON
INSERT INTO Test VALUES (1, 100)
INSERT INTO Test VALUES (2, 100)
INSERT INTO Test VALUES (3, 200)
INSERT INTO Test VALUES (4, null)
SET NOCOUNT OFF
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
I'm not saying that the original poster intended this, nor that it's a
good thing. The warning is harmless most of the times and the best way
to avoid it would be to make that column not nullable. I'm only saying
that the ISNULL-s inside the SUM can make a difference.
Razvan|||You are correct in that the NULL inside the column does prevent the warning.
And it is intended -- I want to ensure I get a value back in the event there
was no value. Alternatively, I could turn the warnings off and/or use
ArithAbort and/or ArithIgnore, but I may just go in and prevent this from
occuring :=)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1122211153.821275.237420@.o13g2000cwo.googlegroups.com...
> David Portas wrote:
> They MAY achieve something: if there are NULL-s in that column, the
> ISNULL from inside the SUM prevents the "Warning: Null value is
> eliminated by an aggregate or other SET operation.". For example:
> CREATE TABLE Test (
> ID int primary key,
> Value int NULL
> )
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> SET NOCOUNT ON
> INSERT INTO Test VALUES (1, 100)
> INSERT INTO Test VALUES (2, 100)
> INSERT INTO Test VALUES (3, 200)
> INSERT INTO Test VALUES (4, null)
> SET NOCOUNT OFF
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> I'm not saying that the original poster intended this, nor that it's a
> good thing. The warning is harmless most of the times and the best way
> to avoid it would be to make that column not nullable. I'm only saying
> that the ISNULL-s inside the SUM can make a difference.
> Razvan
>|||Yep, I could remove the SUM and the GROUP BY, thus negating the entire
reason for the query in the first place, but I'm trying to get the whole
enchilada so I can use the summed values in a report. However I believe you
are correct in thinking there are duplicate rows being created -- I'll dig
into that further. Thanks.
"Chandra" <chandra@.discussions.hotmail.com> wrote in message
news:%23Pk9DQDkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> hi
> you might be getting a lot rows from the query and they are getting
> hidden because of the SUM and GROUP BY,
> please try to remove the SUM and GROUP BY and see the duplicate rows.
> once the duplicate rows are eliminated then u can see the expected
> results again
> best Regards,
> Chandra
> http://groups.msn.com/SQLResource/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***|||This is why you guys are MVPs ... that was perfect, giving me precisely the
results I was looking for.
The inner ISNULLS were being used to prevent warnings (as I noted in the
post below), but I am going to clean that up so no NULLS are possible.
Thanks very much for your help.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:mKidneN_kpIx237fRVn-jQ@.giganews.com...
> It looks like SalesId isn't unique in the LaborJobCosts table. Try the
> following query. Notice that RIGHT JOIN is redundant in your original -
> the WHERE clause turns it into an INNER JOIN anyway. I've also taken out
> the ISNULLs from inside the SUM functions - they don't achieve anything
> except possibly slow things down.
> It helps if you include keys and constraints with your DDL and also post
> INSERT statements for some sample data. What you did post tells us that
> the LaborJobCosts doesn't have any key other than the IDENTITY column.
> That's a potentially serious design flaw.
> SELECT SC.itemsalecode,
> ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
> ISNULL(SUM(LC.laborcost),0) AS laborcosts
> FROM Sales S
> JOIN SaleCodes SC
> ON SC.itemsalecode = S.itemsalecode
> LEFT JOIN
> (SELECT salesid, SUM(laborcost) AS laborcost
> FROM LaborJobCosts
> GROUP BY salesid) AS LC
> ON S.salesid = LC.salesid
> WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
> GROUP BY SC.itemsalecode
> --
> David Portas
> SQL Server MVP
> --
>|||Earl wrote:
> I want to ensure I get a value back in the event there was no value.
As shown by my example, you will get a value even if you DON'T use
ISNULL inside the SUM, as long as you use ISNULL outside the SUM.

> Alternatively, I could turn the warnings off and/or use ArithAbort and/or ArithIgn
ore
Using ArithAbort and/or ArithIgnore will not prevent this warning.
AFAIK, there is no configuration setting to turn off this warning.
Razvan|||> The inner ISNULLS were being used to prevent warnings
Try:
SET ANSI_WARNINGS OFF
David Portas
SQL Server MVP
--