Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2007
    Posts
    14

    Unanswered: Eliminating rows from select results

    Hi,

    is there any way to eliminate a row from the results returned from a select statement?

    I'm using some aggregate functions and doing some division and occassionally I get a divide by zero error. Is there a way I can leave the row out if the divisor is zero? Briefly, my query looks like this:


    select sum(a*b)/sum(b), c
    from TableA
    group by c


    If sum(b) is zero I want to leave that row out.

    Thanks for your help!

    Wallace

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does your database engine supporr the HAVING clause?

    -PatP

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is what HAVING is doing essentially:
    Code:
    SELECT sum_a / sum_b, c
    FROM   ( SELECT SUM(a*b), SUM(b), c
             FROM tableA
             GROUP BY c ) AS t(sum_a, sum_b, c)
    WHERE  sum_b <> 0
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2007
    Posts
    14
    Having should do the trick.

    Thanks for your help!

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by stolze
    This is what HAVING is doing essentially:
    Code:
    SELECT sum_a / sum_b, c
    FROM   ( SELECT SUM(a*b), SUM(b), c
             FROM tableA
             GROUP BY c ) AS t(sum_a, sum_b, c)
    WHERE  sum_b <> 0
    It is answers like the one you provided above that encourage poor software development, in this instance, database development.

    There is no need to create an inline view and filter the result set by applying a where clause to the outer query, when in this instance, the exact same result can be produced by using the Having clause.

    Using the having clause is the preferred method for applying criteria to aggregate results, it was designed for this exact purpose. Although the where clause can be used in certain situations to filter aggregates, it was not designed so.

    To follow your example, perhaps when I provide examples I should replace all column names with some arbitrary name in an attempt to make the solution appear complex and worthy of admiration, when in fact these types of responses would only cause unnecessary confusion to the reader.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    robert, take it easy, he was showing what the HAVING clause does, not suggesting that you avoid using it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.

    stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.

    Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.

    You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by r123456
    You wouldn't speak to your fellow colleagues in such a patronising tone, so why do it here?
    so why are you doing it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    My comments were not said with a patronising tone.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you did not intend them to be, but that is how they came across
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I apologise to all those who may find my comments in this thread rude and patronising. My intent was just to point out that if a simple and easy to understand solution exists, then it should be provided first, before suggesting other less obvious methods.

    That's all.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by wallaceoc

    select sum(a*b)/sum(b), c
    from TableA
    group by c


    If sum(b) is zero I want to leave that row out.
    As already suggested, the following is indeed what you need:
    Code:
    SELECT   sum(a*b)/sum(b), c
    FROM     TableA
    GROUP BY c
    HAVING   sum(b) <> 0
    Note that this also avoids zero division, i.e., the expression sum(a*b)/sum(b) is never executed when sum(b) is 0.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r123456
    I do apologise if my response appears rude and overtly frank. However, I consider it to be totally inappropriate to hijack a thread with the intention to be a "clever dick" and publish a random solution with complete disregard for the original question.

    stolze posted an unnecessary solution to a simple question, which could have easily been answered using a HAVING clause, but did not consider it proper to provide in addition, a solution using the Having clause.
    I just gave an explanation on how HAVING works. That's all...

    Stolze: The poster was not asking for ways to replace the functionality of the Having clause, but instead the question asked how to remove rows from a set, and in this particular instance, the condition was to be applied after the aggregation. In future, perhaps you could assist the poster with their question before trying to prove a point with respect to your technical ability.
    I have no idea why you are reacting so aggressively.

    The OP apparently didn't know about the existence of HAVING and what it does. While the answer given by Pat was correct, I felt it is a good idea to provide more background information.

    I had the chance to give some database courses at universities in the past. One thing I learned there is that students (they being university students or professionals doesn't matter in this respect) first have to learn basic concepts. Later you can use those basic concepts to explain other things. For example, once someone understands sub-selects, he/she will grasp the idea of having-clauses right away if you can show such a reformulated query. And that was my intention here as well. (I don't know where this was "patronizing".)

    Also, I believe it is essential that people working with an RDBMS understand what is going on internally in order to avoid bad queries or to know what can be done for efficiently and what may not be such a great idea. For example, just look at the comments here: http://us2.php.net/odbc_num_rows All those solutions show a distinct lack of understanding of relational database systems. What's my point? It is that a good idea would be that people implement their own simple DBMS because it helps tremendously to understand how a DBMS works internally or how queries can be rephrased. A simple approach to deal with HAVING is to internally rewrite a query to the construct I posted - without loosing any functionality. (At the end of the day, the HAVING clause is very helpful but it just remains a bit syntactic sugar in SQL.) However, I'm fully aware that not everyone has the time or access to the necessary expertise to implement their own small DBMS...

    I suggest that you read a few more threads in this forum and other newsgroup. You will find that questions range from very basic stuff on transactions, the relational model, etc. to the meaning of specific options or error messages and their possible causes. I found that additional explanations are a good way to answer question to the extent that the poster knows how to proceed.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by stolze
    I just gave an explanation on how HAVING works. That's all...
    A very handy explanation... I knew how HAVING works, and I'm sure if someone had asked me I could have shown how it's non-primitive, but I never actually broke it down like that.

    I have no idea why you are reacting so aggressively.
    Even if you could figure out why people say what they do, who cares? It's just words.

Posting Permissions

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