whats wrong in this statement please ? [ SOLVED ]

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

CREATE PROCEDURE dbo.StoredProcedure7
AS
SELECT *
FROM 
(SELECT dbo.Districts.name,count(distinct(dbo.Interview_Schedule.app_id)) AS nom,
            CASE  dbo.Interview_Schedule.interview_result
            WHEN NULL THEN 'no value'
            WHEN 0 THEN 'zero'
            WHEN 1 THEN 'one'
            END AS thevalue
 FROM Districts INNER JOIN
      Interview_Schedule ON Districts.id = Interview_Schedule.interview_district 
 WHERE dbo.Interview_Schedule.work_flow_id=1
 group by dbo.Districts.name)AS datatable
 PIVOT
( COUNT(thevalue)
  FOR thevalue IN ([no value],[zero],[one])) as piv

ERROR ::

Msg 8120, Level 16, State 1, Procedure StoredProcedure7, Line 3
Column 'Interview_Schedule.interview_result' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure StoredProcedure7, Line 3
Column 'Interview_Schedule.interview_result' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Posted 13-Feb-11 13:57pm

MohammedSabry

Updated 15-Feb-11 3:03am

v2

Add a Solution

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

Nish Nishant · Answer 1 · 2011-02-13T14:04:00

Solution 1

Very common error.

See http://social.msdn.microsoft.com/Forums/en/transactsql/thread/dea07916-d7d0-4482-a906-24fd8d567edc[^]

Posted 13-Feb-11 14:04pm

Nish Nishant

Comments

Nish Nishant 13-Feb-11 20:46pm

I am pretty sure you can't add the result of a Count() to a Group By clause.

MohammedSabry 13-Feb-11 20:50pm

so if you please what`s the error in the first statement ??

Nish Nishant 13-Feb-11 20:52pm

Remove dbo.Interview_Schedule.interview_result from the Select. You'll have to get that info through an inner join on the same table. That should fix your problem. Also remove that Count() stuff from the Group By clause - that is not permitted.

MohammedSabry 13-Feb-11 20:58pm

can you write it ?

Nish Nishant 13-Feb-11 21:00pm

I don't really know your table structure and what exactly it is that you are trying to do. Can you describe the columns in the tables and what exactly you are trying to do here? (you can improve your question instead of adding a comment). That way I (or someone else) will be able to help you better.

MohammedSabry 13-Feb-11 21:10pm

TABLE DISTRICTS :
Districts.id, Districts.name

TABLE : Interview_Schedule
Interview_Schedule.app_id, Interview_Schedule.interview_district, Interview_Schedule.interview_result

RELATION:
Districts INNER JOIN
Interview_Schedule ON Districts.id = Interview_Schedule.interview_district

Interview_Schedule.interview_result value can be 0 or 1 or NULL
i want the output from this column to be three columns :
1st : count of (0)
2nd : count of (1)
3ed : count of NULL

Abhinav S · Answer 2 · 2011-02-13T17:43:00

Solution 2

Remove dbo.Districts.name from your query and it should work fine.

Posted 13-Feb-11 17:43pm

Abhinav S

Comments

MohammedSabry 14-Feb-11 9:08am

it didn`t

MohammedSabry · Answer 3 · 2011-02-14T05:49:00

Solution 3

SQL

select D.[Name] as [District Name], sum(case when I.Interview_Result = 1 then 1 end) as [Count of 1],
sum(case when I.Interview_Result = 0 then 1 end) as [Count of 0],
sum(case when I.Interview_Result IS NULL then 1 end) as [Count of NULLs]
from Districts D inner join Interview_Schedule I on D.ID = I.Interview_District
GROUP BY D.[ID],D.[Name]

(copied)

i found it ..

Posted 14-Feb-11 5:49am

MohammedSabry

Comments

Yusuf 14-Feb-11 12:58pm

May be you should update your subject with [SOLVED]. So, people know you got it.