Error in executing sp [Resolved]

Posted by Self-Innovator under Sql Server on 12/18/2012 | Points: 10 | Views : 1353 | Status : [Member] | Replies : 3
hi,


when i run below query in sql server 2005 i am getting erreor

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'

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'

pls solve

Join Hands Change lives
Thanks & Regards
Straight Edge Society



Responses

Posted by: Kirthiga on: 12/19/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi,

While using Aggregate function like 'SUM ','COUNT ' it is necessary to use group by clause

Check the below code with group by clause

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


Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandeepmhatre on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
when you USE aggregate function you need to USE group by clause after END of your where clause

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 '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


Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandeepmhatre on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down

Alternatively you refer description with following link

http://sandeepmhatre.blogspot.com/2012/12/group-by-in-sql-server.html



Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response