Newbie question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Newbie question

Author

Topic

drew22299
Starting Member

26 Posts

Posted - 2009-02-23 : 04:32:16

Hi,

I have a query that select columns from a nested table called t1,

select t1.columnname1, t1.columname2

from

(select columname1, columname2,

count(CASE statement) -- Calculation code here

from
Tablename
table joins) AS t1

The query works when the count(case statement is not in the query but when I include it in the query I get an error saying a column is invalud in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why would including the calculation code cause this error if the query works when the code isn't included?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 04:38:04

you need to specify an aggregate function while using count with some other column. tell your required output. we will try to solve this problem.

Karthik

drew22299
Starting Member

26 Posts

Posted - 2009-02-23 : 04:53:05

Thanks for your reply. This is the calculation code I have so far:

count(CASE when score <=7 then 1 END) AS Score1,
count(CASE when score <=7 then 1 END)/count(*)*100 AS Percentage1,
count(CASE when score >=8 then 1 END) AS Score2,
count(CASE when score >=8 then 1 END)/count(*)*100 AS Percentage2

Next, I need to subtract DetractorsPercentage from PromotersPercentage and store the value in a variable but not sure how to do this.

The error doesn't reference the score column it references a column from a select statement.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 05:05:08

Try this.

select columname1, columname2,
count(CASE statement) -- Calculation code here
from
Tablename
table joins
group by columname1, columname2

Karthik

drew22299
Starting Member

26 Posts

Posted - 2009-02-23 : 05:21:49

I added the columns to the group by clause and it returned a single record whereas before it returned multiple records. I'm guessing this is because there are too many columns in the group by clause?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 05:44:43

No it is just based on your case conditions.

see this example.

declare @B table (columname1 int ,columname2 datetime,score int)
insert into @B VALUES(1,getdate(),1)
insert into @b VALUES(2,getdate() ,8)
insert into @b VALUES(3,null,9)

select columname1,columname2,count(CASE when score <=7 then 1 END) AS Score1 ,
count(CASE when score <=7 then 1 END)/count(*)*100 AS Percentage1,
count(CASE when score >=8 then 1 END) AS Score2,
count(CASE when score >=8 then 1 END)/count(*)*100 AS Percentage2
from @b group by columname1,columname2

Karthik

drew22299
Starting Member

26 Posts

Posted - 2009-02-23 : 09:38:19

Thanks, managed to get the query to work.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-02-23 : 10:42:28

Drew, could you post your resolution? It's possible it could help others with the same type of issue....

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources