asked on

SQL Script Mulitple sums from 2 tables

Hello Experts Exchange
I'm trying to write a SQL script that has three tables, 1 table has a list of all the Area's, the other two tables have the data that I want to sum up.

I have a table with the Area's so that if a Area has no data it comes back as zero.

One table has a column called Total Lost Hours, the other table has Target_Lost_Hours and I want to sum up these by Area.

I have a script which is the following;

select
    cat.[Area], 
    ISNULL([Total Lost Hours],0) AS [Total Lost Hours],
	ISNULL([Target_Lost_Hours],0) AS [Target_Lost_Hours]
from
    [dbo].[MachineLosses_Area] cat left join
    (SELECT   a.[Area], Isnull(SUM(b.[Total Lost Hours]),0) AS [Total Lost Hours], Isnull(SUM(c.[Target_Lost_Hours]),0) AS [Target_Lost_Hours]
    FROM  [dbo].[MachineLosses_Area] a       
    left outer join [dbo].[MachineLosses_View] b on a.[Area] = b.[Area/Line/Machine]
	left outer join [dbo].[TargetLosses_View] c on a.[Area] = c.[Area] and b.[Date] = c.[Date]
    where b.[date] between '01/10/2014' and '20/10/2014'
	GROUP BY a.[Area]
    ) sub on cat.[Area] = sub.[Area]
ORDER BY 
    [Total Lost Hours] DESC

Open in new window

The script comes back but the values are double what they should be.

Can you see where I can change the script to achive my goal please?

Regards

SQLSearcher

Phillip Burton

Can you post the table structure and some sample data please.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQLSearcher

ASKER

Hello Vitor
The script gives me the correct values now, but when a area has 0 in the sum's it does not show that in the query results.

Regards

SQLSearcher

Vitor Montalvão

That's because I used INNER JOIN. Change both INNER JOIN in CTE's to LEFT JOIN and you'll get those zeros.

awking00

While sample data and table structures, along with expected results, would be of great help in determining the best solution, you might be able to use analytic functions and avoid subqueries - what does the following provide?

select a.[Area],
isnull(sum(b.[Total Lost Hours]) over (partition by a.area order by [b.Total Lost Hours] desc),0) as [Total Lost Hours],
isnull(sum(c.[Target Lost Hours]) over (partition by a.area order by [c.Target Lost Hours] desc),0) as [Target Lost Hours]
from [MachineLosses_Area] as a
left outer join [MachineLosses_View] as b
on a.[Area] = b.[Area/Line/Machine]
left outer join [TargetLosses_View] as c
on a.[Area] = c.[Area] and b.[Date] = c.[Date]
where b.[Date] between '01/10/2014' and '20/10/2014';

SQLSearcher

ASKER

Thank you very much for your help.