Solved

SQL Script Mulitple sums from 2 tables

Posted on 2014-10-27
6
178 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 47

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 47

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 61
sql how to count case when 4 16
Create snapshot on MSSQL 2012 3 19
domain login has permission in database, but gets error 3 16
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

810 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