执行sql server sp时出错 [英] Error in executing sql server sp

问题描述

当我在sql server 2005中运行以下查询时我收到错误

When i run below query in sql server 2005 i am getting error

select p.CompanyName,p.ResidentialAddress 'Address',p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName 'Living State',Lct.StateName 'Living City',Nst.StateName 'Native State',
Nct.StateName 'Native City',f.FatherName,f.MotherName,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters
from personalinfo p
left outer join Profession pr on p.Profession=pr.id
left outer join state Nct on p.NativeCityId=Nct.id
left outer join state Nst on p.NativeStateId=Nst.id
left outer join state Lct on p.LivingCityId=Lct.id
left outer join state Lst on p.LivingStateId=Lst.id
left outer join familydetails f on f.customerid=p.customerid
where p.CustomerId='a1a57b79-6585-406f-a5a0-53cf7b9f3f60'

错误：

列''personalinfo.CompanyName''在选择列表中无效，因为它不包含在聚合中函数或GROUP BY子句。

如果我评论这一行它正在工作

Error:
Column ''personalinfo.CompanyName'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
if i comment this line it''s working

Select p.CompanyName,p.ResidentialAddress 'Address',p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName 'Living State',Lct.StateName 'Living City',Nst.StateName 'Native State',
Nct.StateName 'Native City',f.FatherName,f.MotherName,
--sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
--sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters
from personalinfo p
left outer join Profession pr on p.Profession=pr.id
left outer join state Nct on p.NativeCityId=Nct.id
left outer join state Nst on p.NativeStateId=Nst.id
left outer join state Lct on p.LivingCityId=Lct.id
left outer join state Lst on p.LivingStateId=Lst.id
left outer join familydetails f on f.customerid=p.customerid
where p.CustomerId='a1a57b79-6585-406f-a5a0-53cf7b9f3f60'

请解决

please solve

推荐答案

正如Pranathis所说，你正在使用一些不使用group by子句的聚合函数。当您使用group by时，select子句中的每一列必须通过Aggregate函数（如MAX，MIN，AVG，...）定义，或者必须包含在group by子句中。

尝试

As Pranathis indicated you are using some aggregate functions without using group by clause. When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.
Try

select p.CompanyName,p.ResidentialAddress 'Address',p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName 'Living State',Lct.StateName 'Living City',Nst.StateName 'Native State',
Nct.StateName 'Native City',f.FatherName,f.MotherName,
--sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
--sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters
f2.TotalBrothers, f2.TotalSisters
from personalinfo p
left outer join Profession pr on p.Profession=pr.id
left outer join state Nct on p.NativeCityId=Nct.id
left outer join state Nst on p.NativeStateId=Nst.id
left outer join state Lct on p.LivingCityId=Lct.id
left outer join state Lst on p.LivingStateId=Lst.id
left outer join familydetails f on f.customerid=p.customerid
left outer join 
(
	SELECT sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
		   sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters, customerid
	FROM familydetails
	GROUP BY customerid
) f2 ON P.customerid = f2.customerid
where p.CustomerId='a1a57b79-6585-406f-a5a0-53cf7b9f3f60'

select p.CompanyName,p.ResidentialAddress 'Address',p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName 'Living State',Lct.StateName 'Living City',Nst.StateName 'Native State',
Nct.StateName 'Native City',f.FatherName,f.MotherName,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters
from personalinfo p
left outer join Profession pr on p.Profession=pr.id
left outer join state Nct on p.NativeCityId=Nct.id
left outer join state Nst on p.NativeStateId=Nst.id
left outer join state Lct on p.LivingCityId=Lct.id
left outer join state Lst on p.LivingStateId=Lst.id
left outer join familydetails f on f.customerid=p.customerid
where p.CustomerId='a1a57b79-6585-406f-a5a0-53cf7b9f3f60'

这里你使用了一些聚合函数，所以你必须写除了聚合函数之外的所有选定参数的Group by子句。

here your using some aggregate functions, so you must be write a Group by clause with all selected parameters except that aggregate functions.

select p.CompanyName,p.ResidentialAddress 'Address',p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName 'Living State',Lct.StateName 'Living City',Nst.StateName 'Native State',
Nct.StateName 'Native City',f.FatherName,f.MotherName,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalBrothers,
sum(cast(f.BrotherMarried as int))+sum(cast(f.BrotherUnmarried as int)) as TotalSisters
from personalinfo p
left outer join Profession pr on p.Profession=pr.id
left outer join state Nct on p.NativeCityId=Nct.id
left outer join state Nst on p.NativeStateId=Nst.id
left outer join state Lct on p.LivingCityId=Lct.id
left outer join state Lst on p.LivingStateId=Lst.id
left outer join familydetails f on f.customerid=p.customerid
where p.CustomerId='a1a57b79-6585-406f-a5a0-53cf7b9f3f60'

group by p.CompanyName,p.ResidentialAddress ,p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName,Lct.StateName,Nst.StateName ,
Nct.StateName ,f.FatherName,f.MotherName

请你写一下并修改上面的sp根据它，这是非常紧急.. pls pranathis

could u please write and give modify the above sp according to it it''s very emergency..pls pranathis

这篇关于执行sql server sp时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！