SQL Server Forums
Profile | Register | 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?
 New Topic  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
36985 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
30249 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
30249 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000