Annoying group / sum predicament

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

General SQL Server Forums

New to SQL Server Programming

Annoying group / sum predicament

Author

Topic

davidais
Starting Member

17 Posts

Posted - 2012-10-05 : 03:58:56

Hi all,

On this code:

SELECT 
	SupplierAccNo, GRNInDate, AccountNo, SubAddressCode, SUM (Value)
	
FROM
	tbGRNHistory
	LEFT JOIN tbPOrderHistory ON tbGRNHistory.PONumber=tbPOrderHistory.PONumber
	LEFT JOIN tbOrderHistory ON tbPOrderHistory.SONumber=tbOrderHistory.SONumber

WHERE
	tbGRNHistory.SupplierAccNo='OCS336'

GROUP BY
	SupplierAccNo, GRNInDate, AccountNo, SubAddressCode

When I [SUM (Value)] as above I get inaccurate results of hundreds of thousands of $ in a few dozen rows of data.
When I remove the [SUM] from [Value]and put [Value] in [GROUP BY] the values are correct but it does not sum together the values and has hundreds of data rows of small amounts.

How do I have a few dozen lines of correctly summed data?

Many thanks, D

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 04:53:15

[code]SELECT DISTINCT
SupplierAccNo, GRNInDate, AccountNo, SubAddressCode,
SUM (Value) OVER (Partition By SupplierAccNo, GRNInDate, AccountNo, SubAddressCode) SumOfValues

FROM
tbGRNHistory
LEFT JOIN tbPOrderHistory ON tbGRNHistory.PONumber=tbPOrderHistory.PONumber
LEFT JOIN tbOrderHistory ON tbPOrderHistory.SONumber=tbOrderHistory.SONumber

WHERE
tbGRNHistory.SupplierAccNo='OCS336'
[/code]

--
Chandu

davidais
Starting Member

17 Posts

Posted - 2012-10-05 : 05:13:13

thanks but it still returns huge values that are inaccurate...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 05:27:08

quote:
Originally posted by davidais

thanks but it still returns huge values that are inaccurate...

Can you explain more specific?
Provide sample data and also expected result

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources