Solved

SQL Script Mulitple sums from 2 tables

Posted on 2014-10-27
6
165 Views
Last Modified: 2014-10-27
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
0
Comment
Question by:SQLSearcher
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40406046
Can you post the table structure and some sample data please.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40406060
Try it with Common Table Expression:
with Total_CTE (Area, [Total Lost Hours])
as
(SELECT a.[Area], SUM(b.[Total Lost Hours]) 
	FROM  [dbo].[MachineLosses_Area] a       
		inner join [dbo].[MachineLosses_View] b on a.[Area] = b.[Area/Line/Machine]
	where b.[date] between '01/10/2014' and '20/10/2014'
	GROUP BY a.[Area]),
 Targer_CTE (Area, [Target_Lost_Hours])
as	
(SELECT a.[Area], SUM(c.[Target_Lost_Hours])
	FROM  [dbo].[MachineLosses_Area] a       
		inner join [dbo].[TargetLosses_View] c on a.[Area] = c.[Area] 
	where c.[date] between '01/10/2014' and '20/10/2014'
	GROUP BY a.[Area])
select
    cat.[Area], 
    ISNULL(sub1.[Total Lost Hours],0) AS [Total Lost Hours],
	ISNULL(sub2.[Target_Lost_Hours],0) AS [Target_Lost_Hours]
from [dbo].[MachineLosses_Area] cat 
	inner join Total_CTE sub1 on cat.[Area] = sub1.[Area]
	inner join Targer_CTE sub2 on cat.[Area] = sub2.[Area]
ORDER BY sub1.[Total Lost Hours] DESC

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 40406098
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
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40406104
That's because I used INNER JOIN. Change both INNER JOIN in CTE's to LEFT JOIN and you'll get those zeros.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40406283
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';
0
 

Author Closing Comment

by:SQLSearcher
ID: 40406304
Thank you very much for your help.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now