Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2013
    Posts
    10

    Unanswered: How can I select those fields/records which are Greater than zero

    How can I select those fields/records which are Greater than zero means not NULL or not having value as "0"

    The below query fetch records those records also which are Greater than Zero or Zero, I simply want to avoid zero records

    SELECT EMPID, LEGALNAME, SUM( TTK) AS TTK, SUM( MW ) AS MW, SUM( AL ) AS AL

    FROM weeklytable

    WHERE week

    BETWEEN '2013-12-02'

    AND '2013-12-06'

    GROUP BY EMPID

    HAVING (

    SUM( TTK ) >0

    OR SUM( AL ) >0)


    the above query fetches me the below output
    Code:
    EMPID NAME TTK MW AL
    01         Joe  8    12  0
    where as I want if AL is 0 do not show it

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your HAVING clause is the culprit here. You get that row in your resultset because
    Code:
    Sum(TTK) > 0
    you condition check is an OR meaning that either of the statements needs to be true.

    Perhaps you want an AND
    Code:
    HAVING Sum(TTK) > 0
    AND    Sum(AL) > 0
    ?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is it unnecessary to check SUM( MW )?

  4. #4
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by gvee View Post
    Your HAVING clause is the culprit here. You get that row in your resultset because
    Code:
    Sum(TTK) > 0
    you condition check is an OR meaning that either of the statements needs to be true.

    Perhaps you want an AND
    Code:
    HAVING Sum(TTK) > 0
    AND    Sum(AL) > 0
    ?
    gvee I also used AND but when I use AND it means TTK and AL should both return TRUE that is not satisfying as AL value is 0

    hence I get no result from this query

    any other clue

  5. #5
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    Is it unnecessary to check SUM( MW )?
    if I add that still responce is same with AND condition ON if any of the value is "0 " no records are retreived.

    if I use OR condition "0" value field also is selected

    I want a output wheer simply fields with value "0' are not fetched, at the same time records where value is greater than 0 should be retreived.

    something like this

    EMPID NAME TTK MW
    01 Joe 8 12

    here AL is skipped as it was 0
    Last edited by stephen001; 12-19-13 at 09:37.

  6. #6
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by gvee View Post
    Your HAVING clause is the culprit here. You get that row in your resultset because
    Code:
    Sum(TTK) > 0
    you condition check is an OR meaning that either of the statements needs to be true.

    Perhaps you want an AND
    Code:
    HAVING Sum(TTK) > 0
    AND    Sum(AL) > 0
    ?
    gvee thanks for your inputs.

    but when I use AND if any of the field value is 0 condition fails and not a single record is fetched.

    if I use OR I get records which I dont want 0 field records are also retreived.

    I am not getting how can I ignore fields where value is "0"

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Then,
    if your query(except having clause)
    Note: I added AS NAME
    Code:
    SELECT EMPID, LEGALNAME AS NAME, SUM( TTK) AS TTK, SUM( MW ) AS MW, SUM( AL ) AS AL
    FROM weeklytable
    WHERE week
    BETWEEN '2013-12-02'
    AND '2013-12-06'
    GROUP BY EMPID
    returned the intermediate results like...
    Code:
    EMPID NAME  TTK MW AL
    01    Joe   8   12 0 
    02    Anna  0   0  7
    03    Emily 0   5  0
    04    Lucy  1   0  2
    05    Owl   9   0  0
    /*
    so on...
    */
    What final results do yo want?
    Last edited by tonkuma; 12-19-13 at 10:07.

  8. #8
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    Then,
    if your query(except having clause)
    Note: I added AS NAME
    Code:
    SELECT EMPID, LEGALNAME AS NAME, SUM( TTK) AS TTK, SUM( MW ) AS MW, SUM( AL ) AS AL
    FROM weeklytable
    WHERE week
    BETWEEN '2013-12-02'
    AND '2013-12-06'
    GROUP BY EMPID
    returned the intermediate results like...
    Code:
    EMPID NAME  TTK MW AL
    01    Joe   8   12 0 
    02    Anna  0   0  7
    03    Emily 0   5  0
    04    Lucy  1   0  2
    05    Owl   9   0  0
    /*
    so on...
    */
    What final results do yo want?
    Yes tonkuma you are absolutely correct currently

    it shows this only but I want something like


    EMPID NAME TTK MW
    01 Joe 8 12


    I am happy to pass this query by using empid

    clause where I get only one emp those fields

    which are Greater than 0


    EMPID NAME TTK MW
    01 Joe 8 12

  9. #9
    Join Date
    Dec 2013
    Posts
    10
    Yes tonkuma you are absolutely correct currently

    it shows this only but I want something like

    Code:
    EMPID NAME  TTK MW
    01    Joe   8   12
    I am happy to pass this query by using empid

    clause where I get only one emp those fields

    which are Greater than 0

    Code:
    EMPID NAME  TTK MW
    01    Joe   8   12
    in this we can skil AL field which has value 0 for this employee

  10. #10
    Join Date
    Dec 2013
    Posts
    10
    any answers

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    Then,
    if your query(except having clause)
    Note: I added AS NAME
    Code:
    SELECT EMPID, LEGALNAME AS NAME, SUM( TTK) AS TTK, SUM( MW ) AS MW, SUM( AL ) AS AL
    FROM weeklytable
    WHERE week
    BETWEEN '2013-12-02'
    AND '2013-12-06'
    GROUP BY EMPID
    returned the intermediate results like...
    Code:
    EMPID NAME  TTK MW AL
    01    Joe   8   12 0 
    02    Anna  0   0  7
    03    Emily 0   5  0
    04    Lucy  1   0  2
    05    Owl   9   0  0
    /*
    so on...
    */
    What final results do yo want?
    If the intermediate results were OK,

    And you want
    Yes tonkuma you are absolutely correct currently

    it shows this only but I want something like


    Code:
    EMPID NAME  TTK MW
    01    Joe   8   12
    I am happy to pass this query by using empid

    clause where I get only one emp those fields

    which are Greater than 0
    What the rational(or conditions) to select only a row with EMPID = 01?

  12. #12
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    If the intermediate results were OK,

    And you want


    What the rational(or conditions) to select only a row with EMPID = 01?
    Yes I want to add that condition but that will still fetch AL which is 0

    how do I select those records which are NOT 0

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry,
    I couldn't undestand your requiremens entirely.

    I want to see (silently) responses of some other persons who understand your issues(and/or your requirements) more closely.

  14. #14
    Join Date
    Dec 2013
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    Sorry,
    I couldn't undestand your requiremens entirely.

    I want to see (silently) responses of some other persons who understand your issues(and/or your requirements) more closely.
    i am currently getting this output with the following select statement

    Code:
      
    SELECT EMPID, LEGALNAME AS NAME, SUM( TTK) AS TTK, SUM( MW ) AS MW, SUM( AL ) AS AL
    FROM weeklytable
    WHERE week
    BETWEEN '2013-12-02'
    AND '2013-12-06' where empid = 1
    GROUP BY EMPID
    
    EMPID NAME  TTK MW AL
    01    Joe   8   12 0

    i NEED THE BELOW RESULT WHERE AL FIELD IS SKIPPED AS IT WAS 0

    I think this cannot be more clear

    Code:
    EMPID NAME  TTK MW
    01    Joe   8   12

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT EMPID, LEGALNAME AS NAME, SUM( TTK) AS TTK, SUM( MW ) AS MW
     FROM  weeklytable
     WHERE week
           BETWEEN '2013-12-02'
               AND '2013-12-06'
       AND empid = 1
     GROUP BY
           EMPID

Posting Permissions

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