Multiple Columns from WHERE?

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)We're going to take what Bugs Bunny used to call "a left turn at Albuquerque" on you to get to the same place:SELECT Sum(CASE WHEN qty > 100 THEN 1 END) AS HighStock
, Sum(CASE WHEN qty < 20 THEN 1 END) AS LowStock
FROM products-PatP|||Pat Phelan ur logic is gr8!! hats off 2 u|||hey Pat!
thanks a million, it actually worked :)
appreciate your help on that one!|||No problem! Always glad to do what I can to confuse things. ;)

-PatP|||PAt. Ur logic is gr8..Hats off to u|||Oh, that wascally Pat! Ain't he a stinker?|||Oh, that wascally Pat! Ain't he a stinker?He he he he he!

-PatP|||Hey Pat...
is it possible to add a DISTINCT to that sum statement somewhere? like give me the Sum(CASE WHEN qty > 100 THEN 1 END WHERE DISTINCT(myfield))
?
I'm sure that syntax is way off but I hope you get my meaning. I Appreciate your help on this one :)|||Ummm... Nope, clear as mud.

-PatP|||Pat, do you have any possible solutions as a workaround? It seems that I have to split up my query into a temp table get the distinct values into it, then query against the temp table. I was hoping to avoid having to make 4 seperate queries. thanks again|||I don't have any clue what you are asking, so I can't answer. I strongly suspect that there is a way to solve your problem, once I understand what you want.

An example would help a lot, showing a dozen or less rows of data, and the result set that you expect to be returned based on that data.

-PatP