SQL Sum two fields from two different tables

Hello Experts Exchange
I am using SQL Server 2012 and writing a query to sum up to values from two tables, this is my query.

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area/Line/Machine],
        Sum([Total Lost Hours]) as Total_Lost_Hours, Sum([Target_Lost_Hours]) as Target_Lost_Hours
FROM   [dbo].[MachineLosses_View] a
Inner join [dbo].[TargetLosses_View] b
on a.[Area/Line/Machine] = b.[Area]
and a.Date = b.Date
WHERE   CONVERT(DATETIME, a.date, 103) >= '01/04/2014' 
AND     CONVERT(DATETIME, a.date, 103) <= '31/03/2015'
and [Area/Line/Machine] = 'UKL'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area/Line/Machine]
Order by [Area/Line/Machine],DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0)

Open in new window


The value of Target Lost value is too large, and I don't know why.

How do I get a sum of both fields?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How you mean is too large?
Can you give as examples?
0
Daniel WilsonCommented:
Usually a sum that is unbelievably large on a join indicates repeated rows in the resulting join due to a missed join condition.

In your example, if b has more than 1 row to correspond to a row in a, you'll get a bigger sum than you probably expect.

In short, do the join without the sums and with enough fields showing that you can spot duplicates.  Then figure out how to eliminate the duplicates.  Once you've eliminated the duplicates (usually by adding an AND a.anotherfield = b.someotherfield), you can sum and group again and get the right answer.
0
SQLSearcherAuthor Commented:
Hello Vitor
I have attached a spreadsheet with the queries I am running and the results I get, I want the results in sheet2 query to be the results in the query for sheet1.

Regards

SQLSearcher
Sum-Two-Fields.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Total_Lost_Hours is correct? Only Target_Lost_Hours has large values, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also what's the PK  for each of the tables?
As Daniel Wilson said: "if b has more than 1 row to correspond to a row in a, you'll get a bigger sum than you probably expect" this can happen if the JOIN clause isn't really joining all necessary fields.
0
SQLSearcherAuthor Commented:
Hello Vitor
Yes your right Total_Lost_Hours is correct and Target_Lost_Hours has the larger values.

I don have a primary key on the tables.

Is there a way I can run two queries at once and get the results together?

Regards

SQLSearcher
0
Daniel WilsonCommented:
If you add a count(a.date) and count(b.date) to your result, I think you'll find more in one than the other ... showing you where the problem is.

It may be that you need to group each before the join using derived tables.

Something like ...
select * from
(select closing_date=Dateadd(...), [Area...], Sum(Total_Lost_Hours) from MachineLosses_View Where ... GROUP BY Dateadd(...) ) a
inner join
(select closing_date ... from TargetLosses_View Where ...  GROUP BY Dateadd(...)  ) b
on a.area = b.area  and a.closing_date = b.closing_date

Obviously there are a lot of details to fill into that code, but that structure may solve it for you.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don have a primary key on the tables.
Why? Are temporary tables?
Also, the columns used in the query are all the columns on the tables or there are more columns? Maybe you need to join at least one or two columns more.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try if this query helps you:
WITH TL_CTE (Closing_Date, Area, Target_Lost_Hours)
AS
(
SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area],
     Sum([Target_Lost_Hours]) as Target_Lost_Hours
FROM   [dbo].[TargetLosses_View] a
WHERE   CONVERT(DATETIME, a.date, 103) >= '01/04/2014' 
AND     CONVERT(DATETIME, a.date, 103) <= '31/03/2015'
and [Area] = 'UKL'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area])

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area/Line/Machine],
        Sum([Total Lost Hours]) as Total_Lost_Hours, b.Target_Lost_Hours
FROM   [dbo].[MachineLosses_View] a
	Inner join TL_CTE b on a.[Area/Line/Machine] = b.[Area] and a.Date = b.Date
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0),[Area/Line/Machine], b.Target_Lost_Hours
Order by [Area/Line/Machine],DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0)

Open in new window

0
Scott PletcherSenior DBACommented:
First thing is to correct the datetime comparisons.  It should be compared in its native format and not in varchar.  Converting first not only horrendously inefficient but does not yield a valid comparison in format 'dd/mm/yyyy'.  I've used the most accurate method below, using literal dates in format YYYYMMD and a >= and < the next day.

Next, to be sure you don't falsely bloat the total by extra row joins, total each table separately and then join them.

Finally, join the separate totals.  So the query ends up like this:


SELECT a_tots.[Area/Line/Machine], a_tots.Closing_Date, a_tots.Total_Lost_Hours, b_tots.Target_Lost_Hours
FROM (
    SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0), a.[Area/Line/Machine],
            Sum(a.[Total Lost Hours]) as Total_Lost_Hours
    FROM   [dbo].[MachineLosses_View] a
    WHERE a.date >= '20140401'
    AND   a.date <  '20150401'
    and a.[Area/Line/Machine] = 'UKL'
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, a.[Date]), 0), a.[Area/Line/Machine]
) AS a_tots
INNER JOIN (
    SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, b.[Date]), 0), b.[Area],
        Sum(b.[Target_Lost_Hours]) as Target_Lost_Hours
    FROM   [dbo].[TargetLosses_View] b
    WHERE b.date >= '20140401'
    AND   b.date <  '20150401'
    and b.[Area] = 'UKL'
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, b.[Date]), 0), b.[Area]
) AS b_tots ON
    b_tots.[Area] = a_tots.[Area/Line/Machine] AND
    b_tots.Closing_Date = a_tots.Closing_Date
Order by [Area/Line/Machine], Closing_Date
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Hello Scott
Thank you for your help.

Regards

SQLSearcher
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.