Error in executing sp [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

Latest Posts