columns with different where clauses? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

columns with different where clauses?

Author

Topic

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2008-08-12 : 17:13:47

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

38200 Posts

Posted - 2008-08-12 : 17:28:34

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:32:39

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:35:15

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2008-08-13 : 11:59:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources