Sql aggregate function error

Question

0.00/5 (No votes)

See more:

Hello all,

I have select query which is giving me error
Please consider Text marked in bold which is causing this error.

SQL

select ROW_NUMBER() OVER (ORDER BY um.Id) AS [Sr_No],um.DOCRcvdDt as InwardDate, um.ScanId,um.ProposerName as CustomerName,um.RiskStartDt,um.RiskEndDt,um.IntermediaryCode,um.ECNNumber,um.ProposalAmount,Convert(varchar(20),um.DOCRcvdDt)+' '+um.DOCRcvdTime as DOCRcvdDtTime,um.Remarks,bb.BBName as Branch,i.NAME,
	it.[Description] as PaymentMode,r.InstrumentNumber,r.InstrumentDate,r.Amount as InstrumentAmount,a1.dtTime QC1PickedDtTime,ld1.Username as QC1UserId,a2.dtTime QC1DoneDtTime,
	a3.dtTime DEPickedDtTime,a4.dtTime DEDoneDtTime,a5.dtTime QC2PickedDtTime,a6.dtTime QC2DoneDtTime,ld2.Username as DEUserId,ld3.Username as QC2UserId,
	a7.dtTime as PolicyIssuedDtTime,sm.ContactId,sm.ProposalNo,sm.PolicyNo,sm.ReceiptNo,ld4.Username as ScanUserId,count(dm.Id)
	from UploadMaster um
	left Join LoginData ld	on um.EmpId=ld.Id 
	left Join BBMaster bb  	on ld.BBId=bb.Id
	left Join IMD i   	on i.[IMD code]=um.IntermediaryCode
	left Join T_Instrument_Type it 	on it.ID=um.PaymentMode
	left Join Receipt r 	on r.TxnId=um.ScanId
	left Join Aging a1      on a1.UpldMstrId=um.Id and a1.Status='QC1 Picked'
	left Join LoginData ld1 on ld1.Id=a1.UpdatedBy
	left Join Aging a2      on a2.UpldMstrId=um.Id and a2.Status='QC1 Accepted'
	left Join Aging a3      on a3.UpldMstrId=um.Id and a3.Status='DE Picked'
	left Join LoginData ld2	on ld2.Id=a3.UpdatedBy
	left Join Aging a4      on a4.UpldMstrId=um.Id and a4.Status='DE Accepted'
	left Join Aging a5      on a5.UpldMstrId=um.Id and a5.Status='QC2 Picked'
	left Join LoginData ld3	on ld3.Id=a5.UpdatedBy
	left Join Aging a6      on a6.UpldMstrId=um.Id and a6.Status='QC2 Accepted'
	left Join Aging a7	on a7.Status='Policy Issued' and a7.UpldMstrId=um.Id
	left Join StatusMaster sm	on sm.TransactionId=um.ScanId
	left Join LoginData ld4	on ld4.Id=um.EmpId
	left Join DiscrepencyMaster dm	on dm.UploadId=um.Id 
        group by dm.Id
	order by um.ScanId

Error :

SQL

Column 'UploadMaster.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please explain why this error is coming.
And how to sort it.

Thanks
Regards
SUNIL MALI.

Posted 26-Jul-13 23:41pm

sunil mali

Updated 27-Jul-13 20:00pm

pradiprenushe

v3

Add a Solution

Comments

pradiprenushe 28-Jul-13 2:10am

if you are using group by then select statement only contain column from group by, or aggregate function or expression
Google
http://www.google.co.in/search?newwindow=1&q=select+column+not+in+group+by&oq=select+column+group+by&gs_l=serp.3.0.0i7i30l6j0i8i7i30l4.4374.6232.0.8053.7.7.0.0.0.0.168.984.0j7.7.0.crnk_timecombined...0.0..1.1.20.serp.4GcNxYP78oU

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2013-07-28T10:02:00

Solution 2

The only aggregate I can see is count(dm.Id) which is pointless if you also have group by dm.Id
My guess is that you actually need group by um.Id because it's the only one that makes sense when you have ROW_NUMBER() OVER (ORDER BY um.Id)

Posted 28-Jul-13 10:02am

Jörgen Andersson

Maciej Los · Answer 2 · 2013-07-28T10:13:00

I'm assuming that you do not understand what JOIN's do...

Have a look here:
Transact-SQL Joins[^]
Join Hints (Transact-SQL)[^]
Join Fundamentals[^]
Using Inner Joins[^]
Using Cross Joins[^]
Using Inner Joins[^]
Joining Three or More Tables[^]
T-SQL Misconceptions - JOIN ON vs. WHERE[^]
The simplification of JOIN. Simple example of how T-SQL JOIN work[^]
And the best one: Visual Representation of SQL Joins[^]

cherry215 · Answer 3 · 2013-07-27T09:05:00

Hi Sunil Mali...

Just Try adding Group By clause with that particular column in the end like below..
...
...
...
left Join LoginData ld4
on ld4.Id=um.EmpId
left Join DiscrepencyMaster dm
on dm.UploadId=um.Id group by dm.Id
order by um.ScanId
group by UploadMaster.Id

just cos u used aggregate functions in the query its asking for group by clause. it ll show few more columns with same errors..in that case add all of them in group by clause using comas ','. Hope it works...

cheers.

Sql aggregate function error

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0