Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: 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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We're going to take what Bugs Bunny used to call "a left turn at Albuquerque" on you to get to the same place:
    Code:
    SELECT Sum(CASE WHEN qty > 100 THEN 1 END) AS HighStock
    ,  Sum(CASE WHEN qty < 20 THEN 1 END) AS LowStock
       FROM products
    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    7

    Cool

    Pat Phelan ur logic is gr8!! hats off 2 u

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    hey Pat!
    thanks a million, it actually worked
    appreciate your help on that one!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No problem! Always glad to do what I can to confuse things.

    -PatP

  6. #6
    Join Date
    Aug 2004
    Posts
    7

    Question

    PAt. Ur logic is gr8..Hats off to u

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, that wascally Pat! Ain't he a stinker?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Oh, that wascally Pat! Ain't he a stinker?
    He he he he he!

    -PatP

  9. #9
    Join Date
    Jul 2004
    Posts
    5
    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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm... Nope, clear as mud.

    -PatP

  11. #11
    Join Date
    Jul 2004
    Posts
    5
    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

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •