View previous topic :: View next topic
|
Author |
Message |
dibyendumandal11111
New User
Joined: 13 Feb 2017 Posts: 7 Location: india
|
|
|
|
Hi, Need a help-
I have 4 tables tableA, TableB, TableC and TableD
Requirement is like-
Query1:
Select TableA.Col1,Sum(TableC.col2) As Sum1
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
group by TableA.Col1
Query2:
Select TableA.Col1,Sum(TableC.col2) Sum2
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
Inner join TableD
on TableC.Colx = TableD.ColY
group by TableA.Col1
Now with this 2 query I want a result set having columns TableA.Col1,Sum1,Sum2. How can I combine them in a single query. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
It certainly makes no sense to have two queries at first place. The sum will remain same as you are getting it from tableb, the only thing that will change is the total population. Query 2 may have less population because you added tabled to filter.
Instead of stating the problem this way , you can tell is the requirement given to you and expect us to opinion and answer and in that way you could get multiple answers or solutions.
If you still wants to persue this then you could add a union between these teo queries and add a outer select and a case to get 3 columns. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Rohit Umarjikar wrote: |
The sum will remain same as you are getting it from tableb, the only thing that will change is the total population |
Can you explain how the sum from 'tableb' will remain the same. And what did you mean by 'total population'. |
|
Back to top |
|
|
dibyendumandal11111
New User
Joined: 13 Feb 2017 Posts: 7 Location: india
|
|
|
|
Dear Rohit,
How can you say SUM will be same for both the queries?... Second one have an extra inner join.
Anyway with some help I am able to get the desired result.
Consolidated query is like-
Code: |
Select T1.ColXX,Sum1,Sum2
from
(
(Select TableA.Col1 as ColXX,Sum(TableC.col2) As Sum1
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
group by TableA.Col1 ) as T1
inner join
(Select TableA.Col1 as ColXX,Sum(TableC.col2) Sum2
From TableA
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
Inner join TableD
on TableC.Colx = TableD.ColY
group by TableA.Col1 ) as T2
on T1.ColXx= T2.ColXX)
|
Any better option is still welcome.
coded |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
dibyendumandal11111,
Welcome to the forums.
If these tables have large amount of data, you might not want to repeat JOINing A,B, and C in your query.
How about trying to INNER JOIN A,B, and C , LEFT JOIN with D, then work with the result set on an outer query to find Sum1 and Sum2. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
It looks like , I am too much loaded with the work, so please ignore my earlier comment of having "Same Results".
But yes you could have straight LEFT JOIN on tableD and get the second SUM form there and no need to have outer SELECT or anything. Compare the results with your other solution.
Code: |
Select a.Col1,Sum1,b.Sum2
From TableA a
Inner join TableB
on TableA.Colx = TableB.ColY
Inner join TableC
on TableB.Colx = TableC.ColY
left outer join TableD b
on TableC.Colx = TableD.ColY
group by a.Col1 |
|
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Rohit Umarjikar,
Where did 'Sum1' and 'Sum2' come from? Do you realize those are not column names and have to be calculated.
Do you think 'GROUP BY' will work without an aggregate function in the select clause in this context? |
|
Back to top |
|
|
|