Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: sum 2 columns?

  1. #1
    Regular Coder
    Join Date
    May 2004
    Posts
    391
    Thanks
    76
    Thanked 0 Times in 0 Posts

    sum 2 columns?

    I'm tryig to get the sum of total time spent on each activity worked by employees. Below is the sql I have but it does not sum. I need to sum hours and minutes to get a total amount of time spent on each ACTIVITY for a date range? How can I do this?

    Code:
    SELECT *,SUM(HOURS),SUM(MINUTES)
                   FROM DAILY_ACTIVITY 
                   WHERE  ACTIVITY_DATE  >= '2011-03-03'
                      AND ACTIVITY_DATE <= '2011-04-11'     
                   AND HOURS + MINUTES > 0
                  AND USERID  = '36'
    GROUP BY ACTIVITY

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Your GROUP BY needs to specify *ALL THE FIELDS* that you want to group on.

    In general, that should be all the fields in your SELECT statement (other than the ones in the aggregate functions...SUM and COUNT and MAX and MIN, etc., are all aggregate functions).

    So:
    Code:
    SELECT ACTIVITY,SUM(HOURS),SUM(MINUTES)
                   FROM DAILY_ACTIVITY 
                   WHERE  ACTIVITY_DATE  >= '2011-03-03'
                      AND ACTIVITY_DATE <= '2011-04-11'     
                   AND HOURS + MINUTES > 0
                  AND USERID  = '36'
    GROUP BY ACTIVITY
    In general, you should never use SELECT * when working with GROUP BY. It's almost impossible to get the results you need/expect if you do so.

    I'm not sure what the purpose of AND HOURS + MINUTES > 0 is. If their values are zero, so what? Then they won't add anything to the SUM() results.

    Finally, that query is going to get you something like
    Code:
    breathing 196 1221
    eating     13  493
    That is, 196 hours and 1221 minutes, etc.

    Wouldn't it be more reasonable to get hours and minutes as an OVERALL total?

    So:
    Code:
    SELECT ACTIVITY, SUM(HOURS * 60 + MINUTES)/60.0 AS totalHours
                   FROM DAILY_ACTIVITY 
                   WHERE  ACTIVITY_DATE  >= '2011-03-03'
                      AND ACTIVITY_DATE <= '2011-04-11'     
                   AND HOURS + MINUTES > 0
                  AND USERID  = '36'
    GROUP BY ACTIVITY
    And so get something like
    Code:
    breathing 216.35
    eating     21.17
    ???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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