SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Annoying group / sum predicament
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidais
Starting Member

17 Posts

Posted - 10/05/2012 :  03:58:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 10/05/2012 :  04:53:15  Show Profile  Reply with Quote
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'



--
Chandu

Edited by - bandi on 10/05/2012 04:54:49
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/05/2012 :  05:13:13  Show Profile  Reply with Quote
thanks but it still returns huge values that are inaccurate...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 10/05/2012 :  05:27:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000