February 12, 2008 at 1:42 pm
Ok, I've got to get a few sums to add to a return....let me try to write out what I need below, and keep in mind I know what's wrong with what's below, but now how to fix it....
SELECT
SUM(Table1.Column1) AS FirstValue,
SUM(Table3.Column1) AS FourthValue,
SUM(Table4.Column1) AS FifthValue,
SUM(Table2.Column9) AS SecondValue,
SUM(Table5.Column9) AS ThirdValue
FROM MainTable
LEFT JOIN DataTable Table1 ON MainTable.ID = Table1.ID AND Table1.Type = 'A'
LEFT JOIN DataTable Table3 ON MainTable.ID = Table3.ID AND Table3.Type = 'B'
LEFT JOIN DataTable Table4 ON MainTable.ID = Table4.ID AND Table4.Type = 'C'
LEFT JOIN OtherTable Table2 ON MainTable.ID = Table2.ID AND Table2.Type = 'DD'
LEFT JOIN OtherTable Table5 ON MainTable.ID = Table5.ID
So giving the above example, and with the way I'm joining the data naturally my Sums aren't correct, since the Joins can return any given number of rows and thereby skew the "SUM" values. The question is....how do I basically get accurate SUMS for these columns from each table without one table screwing up the other like I am with the above statement
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply