simple sql stetment with 2 SUM - wrong result :-(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

simple sql stetment with 2 SUM - wrong result :-(

Author

Topic

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-01 : 14:54:51

Hi guys,

here's a simple question that drives me crazy.

when I do this,

SELECT SUM(CSV_net.cur_amount) AS net_Accumulated
from csv_net_production as csv_net
where ID='5530500'
GROUP BY ID

I get: 35188 //this is the right answer.

but when I'm doing this:

SELECT SUM(CSV_net.cur_amount) AS net_Accumulated, SUM(CSV.cur_amount) AS accumulated
from csv_net_production AS csv_net, csv_production AS CSV
where csv.ID='5530500'
AND csv_net.ID=csv.ID
GROUP BY csv.ID

I get:
598196 607476 //wrong anser.

can anyone advise why and how to correct it?
thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-01 : 17:26:58

You need to put a join condition for the two tables being used.

************************
Life is short. Enjoy it.
************************

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-01 : 23:20:42

thanks but I'm not sure where to put the join condition.
I tried somthing like this:

SELECT SUM(CSV.cur_amount) AS Accumulated, SUM(CSV_net.cur_amount) AS net_accumulated
from csv_production AS csv

RIGHT JOIN csv_net_production AS CSV_net ON CSV_net.ID=CSV.ID

where csv.ID='5530500'
GROUP BY csv.ID, csv_net.ID

but I get the same result

any idea??!?!?!?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 23:33:39

[code]
select Accumulated, net_accumulated
from
(
SELECT SUM(CSV.cur_amount) AS Accumulated
FROM csv_production AS csv
WHERE csv.ID = '5530500'
GROUP BY csv.ID
) a
cross join
(
SELECT SUM(CSV_net.cur_amount) AS net_accumulated
FROM csv_net_production AS csv_net
WHERE csv_net.ID = '5530500'
GROUP BY csv_net.ID
) b
[/code]

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-01 : 23:53:44

Thank you KH,

Your solution is correct. I'm trying to integrate your solution with my original query:

SELECT TR.ID, TA.Name, TR.idTarget, TR.Controller_Status, TR.previuosYear, TR.deduction, TR.idProduction, SUM(CSV.cur_amount) AS Accumulated, SUM(CSV_net.cur_amount) AS net_Accumulated, PD.Name AS PName, PD.type_2, CL.ToRange AS growth
FROM agent AS TA, production_name AS PD, calculation AS CL, target AS TR

RIGHT JOIN csv_production AS CSV ON CSV.ID=TR.ID AND CSV.Production_Date BETWEEN '2006-10-01' AND '2006-12-31'

RIGHT JOIN csv_net_production AS CSV_net ON CSV_net.ID=TR.ID AND CSV_net.Production_Date BETWEEN '2006-10-01' AND '2006-12-31'

WHERE TA.ID=TR.ID

AND PD.idProduction = TR.idProduction
AND CL.idCalculation = ANY (SELECT MIN(idCalculation) FROM calculation GROUP BY idProduction)
AND TR.idProduction=CL.idProduction
AND TR.startDate= '2006-10-01' AND TR.endDate='2006-12-31'

GROUP BY CSV.ID

the problem is with the second right join. how do I do the cross join to have a correct sql syntax?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 00:11:54

what is the relationship for csv_production and csv_net_production ? is it ONE to ONE ? on column ID ?

Maybe it will be clearer if you can post your table DDL, some sample data and the expected result.

xianwinwin
Starting Member

17 Posts

Posted - 2007-04-02 : 00:50:19

> what is the relationship for csv_production and csv_net_production ? >is it ONE to ONE ? on column ID ?

Yes, the common column of csv and csv_net is the ID. it has the same structure but diff numbers.

>Maybe it will be clearer if you can post your table DDL, some sample >data and the expected result.

hope the images help, note that the numbers for accumulated is incorrect in the "WRONG DATA" this is because of the asdditional csv_net.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 01:56:53

Dupe
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81567

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources