February 12, 2008 at 1:50 pm
You could use derived tables for each sum and do a left join.
Some thing like
Select Ix.D, a.FirstVale, c.FourthValue, d.FifthValue
from MainTable as x
Left Join
(Select Sum(Column1) from Table1) as a
on x.ID = a.ID and a.Type = 'A'
Left Join
(Select Sum(Column1) from Table3) as c
on x.ID = c.ID and c.Type = 'B'
Left Join
(Select Sum(Column1) from Table5) as d
on x.ID = d.ID and d.Type = 'C'
-Roy
February 12, 2008 at 2:06 pm
You could also union all the query results together like this:
SELECT 'First',SUM(Table1.Column1) AS [Sum]
FROM MainTable
LEFT JOIN DataTable Table1
ON MainTable.ID = Table1.ID AND Table1.Type = 'A'
UNION ALL
SELECT 'second',SUM(Table2.Column9) AS [SecondValue]
FROM MainTable
LEFT JOIN OtherTable Table2
ON MainTable.ID = Table2.ID AND Table2.Type = 'DD'
UNION ALL
select 'third',SUM(Table5.Column9) AS ThirdValue
FROM MainTable
LEFT JOIN DataTable Table3
ON MainTable.ID = Table3.ID AND Table3.Type = 'B'
--ETC...
February 12, 2008 at 2:08 pm
Roy had the right idea...but I think forgot a few pieces along the way.
Select Ix.D, a.FirstVale, b.FourthValue, d.FifthValue
from MainTable as x
Left Join
(Select id, Sum(Column1) from Table1 where type='A' GROUP BY id) as a
on x.ID = a.ID
Left Join
(Select iD, Sum(Column1) from Table3 where type='B' GROUP BY id) as b
on x.ID = b.ID Left Join
(Select id, Sum(Column1) from Table5 where type='d' GROUP BY id) as d
on x.ID = d.ID
You can't link on things that aren't included in the subquery.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 2:10 pm
Adam Haines (2/12/2008)
You could also union all the query results together like this:
...except you don't get the results displayed in the way the OP requested the data....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 2:12 pm
Ah Yes Matt, you are right. Thats what happens when you start typing the query in without looking at it carefully:hehe:. My bad..
-Roy
February 12, 2008 at 2:12 pm
cool, I got it working, thanks guys.
February 12, 2008 at 2:36 pm
Roy Ernest (2/12/2008)
Ah Yes Matt, you are right. Thats what happens when you start typing the query in without looking at it carefully:hehe:. My bad..
Hey - I WROTE the T-shirt that talks about the "been there done that" for that one....hehe.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply