April 27, 2009 at 1:18 pm
Mayank Khatri (4/27/2009)
Run Below:SELECT p.name, SUM(s.cost) AS Software, SUM(cast(isnull(m.cost,0)as int)) AS Material,
SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idSoftware
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
I clean up the database and insert this values :
Insert into TProject values ('test',1000,'2008')
Insert into TSoftware values(1,'april',2000,'test','MS')
Insert into THardware values (1,'april',10000,'test','test')
Insert into TMaterial values (1,'april',2000,'test')
Insert into TSoftware values(1,'april',100000,'test','MS')
Insert into TTravel values (1,'April',1000,'test', 'test')
Then I used the query in the quote above and again I recevied wrong results! 🙁
This are the correct results:
test 102000.00 2000.00 1000.00 10000.00
This are the wrong results that the query is showing:
test 102000.00 4000 2000.00 20000.00
April 27, 2009 at 1:56 pm
moramoga (4/27/2009)
Mayank Khatri (4/27/2009)
Run Below:SELECT p.name, SUM(s.cost) AS Software, SUM(cast(isnull(m.cost,0)as int)) AS Material,
SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idSoftware
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
I clean up the database and insert this values :
Insert into TProject values ('test',1000,'2008')Insert into TSoftware values(1,'april',2000,'test','MS')
Insert into THardware values (1,'april',10000,'test','test')
Insert into TMaterial values (1,'april',2000,'test')
Insert into TSoftware values(1,'april',100000,'test','MS')
Insert into TTravel values (1,'April',1000,'test', 'test')
Then I used the query in the quote above and again I recevied wrong results! 🙁
This are the correct results:
test 102000.00 2000.00 1000.00 10000.00
This are the wrong results that the query is showing:
test 102000.00 4000 2000.00 20000.00
I have used the same query and I am getting results as follows:
test 2000.00 2000 1000.00 10000.00
Please note the query which I have modifies has a different join condition in TSoftware s.
I have there s.idSoftware.
April 27, 2009 at 2:07 pm
HI!,
SELECT p.name, SUM(s.cost) AS Software, SUM(cast(isnull(m.cost,0)as int)) AS Material,
SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idSoftware
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
If I run the query exactly how you post it above, my Software column is always Null even if I have cost assigned to it. The other results are okay. But if I change the join from p.idProyect = s.idSoftware to p.idProyect = s.idProject all the results are wrong again 🙁
April 28, 2009 at 2:12 am
SELECT p.Name, sCost, mCost, hCost, tCost
FROM TProject p
OUTER APPLY (SELECT SUM(Cost) as sCost,idProject FROM TSoftware WHERE idProject = p.idProyect GROUP BY idProject) s
OUTER APPLY (SELECT SUM(Cost) as mCost,idProject FROM TMaterial WHERE idProject = p.idProyect GROUP BY idProject) m
OUTER APPLY (SELECT SUM(Cost) as hCost,idProject FROM THardware WHERE idProject = p.idProyect GROUP BY idProject) h
OUTER APPLY (SELECT SUM(Cost) as tCost,idProject FROM TTravel WHERE idProject = p.idProyect GROUP BY idProject) t
This will work... do you know why? if not I'll try and explain
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 8:20 am
HI! It is finally working! I didnt know that it will be so complicated, when I thought of it I remeber thinking what a piece of cake lol...thanks everyone for helping!!
April 28, 2009 at 8:22 am
if it's null it's because there are no costs for that column.
You could use isnull around the cost values to so Zero or add a where clause to exlude rows that don't have values
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 8:26 am
Yes sorry, my bad! I already edited my last post:-D. Thanks a lot for the help!
April 29, 2009 at 11:56 am
Christopher Stobbs (4/28/2009)
SELECT p.Name, sCost, mCost, hCost, tCost
FROM TProject p
OUTER APPLY (SELECT SUM(Cost) as sCost,idProject FROM TSoftware WHERE idProject = p.idProyect GROUP BY idProject) s
OUTER APPLY (SELECT SUM(Cost) as mCost,idProject FROM TMaterial WHERE idProject = p.idProyect GROUP BY idProject) m
OUTER APPLY (SELECT SUM(Cost) as hCost,idProject FROM THardware WHERE idProject = p.idProyect GROUP BY idProject) h
OUTER APPLY (SELECT SUM(Cost) as tCost,idProject FROM TTravel WHERE idProject = p.idProyect GROUP BY idProject) t
This will work... do you know why? if not I'll try and explain
Hi,
Can you explain why this work?
May 12, 2009 at 2:32 am
Hi Again,
Sorry I have been on holiday for the last 2 weeks 🙂
Do you still an explanation?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 15, 2009 at 5:41 am
if the Hardware ,software,material table is directly used then it will take duplicate records for aggregate. for eg : for prj id 1 software has 3 records; hardware has only 1 record. then output of the first query (remove aggregate and group by ) would look like this.
prj S H
1 1000 1000
1 2000 1000
1 3000 1000
and on group by PrjId it will sum up this data giving wrong total
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply