WHERE criteria - 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

WHERE criteria

Author

Topic

chobes
Starting Member

6 Posts

Posted - 2007-11-28 : 21:36:13

SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings
Where WinningPct > 0.5

SQL will not allow me to put a column that I created(WinningPct) as criteria for WHERE (I know this is cause Select is evaluated last)

How can I list my results according to criteria I am creating in my query?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-28 : 21:46:51

Just put the expression in the WHERE clause too. If you're using SQL Server 2005, look up Common Table Expressions for more complex cases.

SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings 
Where Wins/Games > 0.5

chobes
Starting Member

6 Posts

Posted - 2007-11-28 : 21:57:45

Yes, my case is more complicated than this. The criteria I wanted to use is Count(something)/Count(distinct somethingelse) -This is in my Select line cause obviously I want to return those values, but I also want to only return those that are greater than 0.500

Where can I look these complex cases up? where is these Common Table Expressions?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-28 : 22:04:47

Then (if you're using aggregates in your expression) you must the criteria in the HAVING clause, not the WHERE clause.
Try the SQL Server documentation Books Online or MSDN here
http://msdn2.microsoft.com/en-us/library/ms190766.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 01:48:40

quote:
Originally posted by chobes

Yes, my case is more complicated than this. The criteria I wanted to use is Count(something)/Count(distinct somethingelse) -This is in my Select line cause obviously I want to return those values, but I also want to only return those that are greater than 0.500

Where can I look these complex cases up? where is these Common Table Expressions?

Can you post the exact query you used?
CTE is available in SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

chobes
Starting Member

6 Posts

Posted - 2007-11-29 : 01:53:30

Got what I needed there. Thanks for the help.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-29 : 06:56:15

quote:
Originally posted by chobes

Yes, my case is more complicated than this. The criteria I wanted to use is Count(something)/Count(distinct somethingelse) -This is in my Select line cause obviously I want to return those values, but I also want to only return those that are greater than 0.500

Where can I look these complex cases up? where is these Common Table Expressions?

in this condition whta i would do is

declare @var1 float
declare @var2 float
select @var1=count(t1.id) from testing1 t1
select @var2=count(t2.id)from testing2 t2
select @var1/@var2

use last line's value for where condition

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-29 : 06:57:14

quote:
Originally posted by chobes

Got what I needed there. Thanks for the help.

it will be very kind of yours if u plz let me know your solution.

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-29 : 07:17:28

select @var2=count(t2.id)from testing2 t2

so what happens when @var2 is zero?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-29 : 16:09:11

quote:
Originally posted by arorarahul.0688
select @var1/@var2

Yet another case of not reading before posting an answer.

This won't work because it doesn't solve the original problem.

Read the post and think about the question before providing an answer. All of the wrong answers you've posted don't help anyone, and can cause other people problems because they don't understand you're wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources