?
Solved

SQL Script Mulitple sums from 2 tables

Posted on 2014-10-27
6
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 2000 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
Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

 
LVL 51

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 32

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

765 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