Results 1 to 4 of 4

Thread: Select With Sum

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Question Unanswered: Select With Sum

    I have a SQL Statement that returns to me 2 records where the value of the [PaidHours] > 0. However, when I change the qualifier to > 2, it only gives me 1 record when in fact the other record has a value of 2.04 (SEE BELOW).

    Is there any reason why the other record is not shown?



    Code:
    SELECT 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101) AS VRUServiceDate,
       SUM(PaidHours) AS PaidHours 
    FROM Workorder 
    WHERE 
       DeptCode=89 
       AND PaidHours>0
    GROUP BY 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101)
    RESULTS:
    EmployeeID VRUServiceDate PaidHours
    ----------- -------------- ----------------------------------------
    10200 06/09/2003 2.58
    10217 06/30/2003 2.04

    (2 row(s) affected)

    Code:
    SELECT 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101) AS VRUServiceDate,
       SUM(PaidHours) AS PaidHours 
    FROM Workorder 
    WHERE 
       DeptCode=89 
       AND PaidHours>2
    GROUP BY 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101)
    RESULTS:
    EmployeeID VRUServiceDate PaidHours
    ----------- -------------- ----------------------------------------
    10200 06/09/2003 2.58

    (1 row(s) affected)

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: Select With Sum

    Yes there certainly is.. If your PaidHours > 2 is in your WHERE clause it will only pick up rows where PaidHours is greater than 2... for instance..

    EmployeeID VRUServiceDate PaidHours
    ----------- -------------- ----------------------------------------
    10200 06/09/2003 2.58 --> Greater than 2, returned
    10217 06/30/2003 1.04 --> Not reater than 2, not returned
    10217 06/30/2003 1.00 --> Not greater than 2, not returned


    What you need to do is put yourPaid hours > 2 in a having clause..

    Code:
    SELECT 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101) AS VRUServiceDate,
       SUM(PaidHours) AS PaidHours 
    FROM Workorder 
    WHERE 
       DeptCode=89 
    GROUP BY 
       EmployeeID,
       CONVERT(CHAR(10),VRUServiceDate,101)
    HAVING PaidHours>2

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I believe that SQL Server is interpreting the value 2 as an integer, and is casting your PaidHours value as an integer in order to do the compare. 2.04 converts to 2 as in integer, which is equal to and not greater than your criteria.

    You can change it to >=2, or it would probably work if you changed it to >2.0, which would let the query optimizer compare decimal to decimal values.

    By the way, if you want to filter on the sum of paid hours, put your criteria in the haveing clause, otherwise leave it where it is.

    blindman
    Last edited by blindman; 07-09-03 at 13:17.

  4. #4
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Thanks. I will check on this.

    I use VB for the front end and when I call the same SQL Syntax via SPROC, it does give me back the 2 records.

    The difference is when I run it on the SQL Query Analyzer as I pointed out gives back only 1 record and skips the 2.04.

Posting Permissions

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