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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 columns with different where clauses?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 08/12/2008 :  17:13:47  Show Profile  Reply with Quote
How would you approach this?

I want 3 different columns cold days,hot days, and normal days

cold days => temperature less than 50 degrees
hot days => temperature greater than 90 degrees
normal days -> normal days => temperature between 50 -90 degrees

All these come from the same table and the only thing that changes the query is the where clause. How would I assign the where clause to each column??

This is what I have so far... but it doesn't seem right?

select temperatures.temp as "cold days",temperatures.temp as "hot days",temperatures.temp as "normal days"
from temperatures
where (temperature < 50)
and (temperature > 90)
and (temperature between 50 and 90)




tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 08/12/2008 :  17:28:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
What happens if there are 2 cold days, 4 normal days, and 1 hot day? Are you going to show NULL when it doesn't apply?

Here's the scenario I'm referring to:

ColdDays	NormalDays	HotDays
20		x		x
33		x		x
x		x		110
x		75		x
x		82		x
x		74		x
x		63		x


So what are you going to show for "x"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 08/12/2008 :  17:32:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	SUM(CASE WHEN Col1 < 50 THEN 1 ELSE 0 END) AS ColdDays,
	SUM(CASE WHEN Col1 BETWEEN 50 AND 90 THEN 1 ELSE 0 END) AS NormalDays,
	SUM(CASE WHEN Col1 > 90 THEN 1 ELSE 0 END) AS HotDays
FROM	Table1



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 08/12/2008 :  17:35:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	CASE WHEN Temp < 50 THEN Temp ELSE NULL END AS ColdDays,
	CASE WHEN Temp BETWEEN 50 AND 90 THEN Temp ELSE NULL END AS NormalDays,
	CASE WHEN Temp > 90 THEN Temp ELSE NULL END AS HotDays
FROM	Temperatures



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 08/13/2008 :  11:59:45  Show Profile  Reply with Quote
yeah, I think I would use nulls in place of the x's.

I need all 3 columns, hot days, cold days and normal days

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000