Sql聚合函数错误 [英] Sql aggregate function error

问题描述

大家好，





我有选择查询给我错误

请考虑文字标记为粗体，导致此错误。

Hello all,


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

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 :

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

请解释原因这个错误即将来临。

如何排序。



谢谢

问候

SUNIL MALI。

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

Thanks
Regards
SUNIL MALI.

推荐答案

我能看到的唯一聚合是 count（dm.Id）哪个如果你还有 group by dm.Id

毫无意义我猜你实际上需要 group by um.Id 因为当你有 ROW_NUMBER（）OVER（ORDER BY um.Id）

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)

时，它是唯一有意义的

我假设你不明白JOIN的做法...



看看这里：

Transact-SQL联接 [ ^ ]

加入提示（Transact-SQL） [ ^ ]

加入基础知识 [ ^ ]

使用内部联接 [ ^ ]

使用Cross加入 [ ^ ]

使用内部联接 [ ^ ]

加入三个或更多表 [ ^ ]

T-SQL误解 - JOIN ON与WHERE [ ^ ]

JOIN的简化。 T-SQL JOIN如何工作的简单示例 [ ^ ]

最好的一个： SQL连接的可视化表示 [ ^ ]

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[^]

Hi Sunil Mali。 ..



只需尝试在下面的特定列中添加Group By子句，如下所示..

...

...

...

left加入LoginData ld4

on ld4.Id = um.EmpId

离开加入DiscrepencyMaster dm

on dm.UploadId = um.Id group by dm.Id

order by um.ScanId

UploadMaster.Id分组



只是你在查询中使用了聚合函数要求分组条款。它会显示更多具有相同错误的列。在这种情况下，使用comas'，'在group by子句中添加所有列。希望它有效...



干杯。

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聚合函数错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！