Error in executing sql server sp

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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

SQL

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

Posted 18-Dec-12 0:14am

sahmed4

Updated 18-Dec-12 0:18am

__TR__

v2

Add a Solution

4 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)

__TR__ · Answer 1 · 2012-12-18T00:28:00

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

SQL

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'

pranathis012 · Answer 2 · 2012-12-18T00:20:00

SQL

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'

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

SQL

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

sahmed4 · Answer 3 · 2012-12-18T00:28:00

Solution 2

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

Posted 18-Dec-12 0:28am

sahmed4

Comments

__TR__ 18-Dec-12 6:30am

This is not an answer. Use "Have a Question or Comment?" button to ask any specific question and delete this solution to avoid getting down-voted.

sahmed4 · Answer 4 · 2012-12-18T01:02:00

Hi i've also tried thanks a lot....below is my SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [C244570_marriage].[sp_GetContactDetails]
@UserId nvarchar(max)
as
begin
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(brothermarried as int))+sum(cast(brotherunmarried as int)) 'Total Brothers',
sum(cast(sistermarried as int))+sum(cast(sistersunmarried as int)) 'Total Sisters'
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=@UserId
group by
p.CompanyName,p.ResidentialAddress,p.DateOfBirth,p.Age,p.PhoneNo,p.MobileNo,p.Education,
pr.Profession,Lst.StateName,Lct.StateName,Nst.StateName,
pr.profession,lst.StateName,lct.statename,nst.statename,nct.statename,
f.fathername,f.mothername,f.brothermarried,f.brotherunmarried,f.sistermarried,f.sistersunmarried
end

Error in executing sql server sp

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0