Error in aggrigate SQL - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Error in aggrigate SQL

Author

Topic

Dajer
Starting Member

22 Posts

Posted - 2009-08-26 : 02:10:01

Hi friends
I have written an sql as following.but It has Error.where is my problem?

  @DocNum1 nvarchar(50),
	@DocNum2 nvarchar(50),
	@DocDate1 nvarchar(13),
	@DocDate2 nvarchar(13),
	@MoeCode1 nvarchar(6),
	@MoeCode2 nvarchar(6),
	@Office int
AS

select DocNum,DocDate,DocDetails,Debtor,Creditor,abs((Sum(Debtor)- Sum(Creditor))) as 'remain',Document.FacNum,Document.Date,[Value/Count],UnitPrice 
		from Document 
		where (MoeCode between @MoeCode1 and @MoeCode2) and
		(DocNum between @DocNum1 and @DocNum2) and (DocDate between @DocDate1 and @DocDate2) and OfficeID=@Office

the Error is:
Column "Document.DocNum" is invalid in the select list because it is not contained in either an aggrigate function or the Group By clause.

thx for your attention

Sara Dajer

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-26 : 02:18:46

Use Group by clause.

select DocNum,DocDate,DocDetails,Debtor,Creditor,abs((Sum(Debtor)- Sum(Creditor))) as 'remain',Document.FacNum,Document.Date,[Value/Count],UnitPrice
from Document
where (MoeCode between @MoeCode1 and @MoeCode2) and
(DocNum between @DocNum1 and @DocNum2) and (DocDate between @DocDate1 and @DocDate2) and OfficeID=@Office
group by DocNum,DocDate,DocDetails,FacNum,Date,Value,Count,UnitPrice

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources