Solved

SQL Script Mulitple sums from 2 tables

Posted on 2014-10-27
6
172 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 46

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 46

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

947 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

22 Experts available now in Live!

Get 1:1 Help Now