+ Post New Thread
Results 1 to 9 of 9
Coding Thread, SQL - Dates and SUM in Coding and Web Development; Code: SELECT s.fullname AS userfullname,SUM(o.numberdays) AS daystotal, o.datefrom from occasions o INNER JOIN tra.staff s ON o.userid=s.id GROUP BY o.userid ...
  1. #1

    Join Date
    May 2009
    Location
    Leeds
    Posts
    22
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    11

    SQL - Dates and SUM

    Code:
    SELECT s.fullname AS userfullname,SUM(o.numberdays) AS daystotal, 
    o.datefrom from occasions o INNER JOIN tra.staff s ON o.userid=s.id GROUP BY 
    o.userid HAVING DATE_SUB(curdate(),INTERVAL 1 MONTH) <= `datefrom`
    Quick question related to the code above:

    I'm trying to add up the contents from a column (the SUM part) from a certain date range - in this case one month.
    My problem is that it does the sum calculation first, so whatever date range I put in it will add all the contents of the columns - any suggestions?

    T
    Last edited by Tim_S; 9th February 2010 at 10:55 AM.

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    i'm no whizz, but i recall that you can pass a select statement inside a function in order for it to act on the results, or did i just make that up.

    so you could go sum(select o.numberdays . . . )

  3. #3

    Join Date
    May 2009
    Location
    Leeds
    Posts
    22
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    11
    Thanks for the direction, will give that a try at least...

  4. #4

    Join Date
    May 2009
    Location
    Leeds
    Posts
    22
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    11
    Tried that to no avail (doesn't seem to work with SUM as subqueries can only return 1 row)

    Think I might have to try creating temp tables.

  5. #5


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,473
    Thank Post
    866
    Thanked 848 Times in 670 Posts
    Rep Power
    196
    1. What database? Looks mysql-y
    2. Give us a select * limit 5 from the tables
    3. why "having" and not "where"?

    I will try and have a look when I get home... too busy in the office today :'(

  6. #6

    Join Date
    May 2009
    Location
    Leeds
    Posts
    22
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    11
    Quote Originally Posted by tom_newton View Post
    1. What database? Looks mysql-y
    2. Give us a select * limit 5 from the tables
    3. why "having" and not "where"?

    I will try and have a look when I get home... too busy in the office today :'(
    Good questions.

    MySQL. It's a custom one for work, so not trying to extract from SIMS or anything.

    Occasions
    ---------
    id, userid, datefrom, dateto, numberdays, type, reason, note
    "10","1","2010-02-02","2010-02-03","1.00","selfcert","sickness","ytjfhtht"
    "11","1","2010-02-05","2010-02-09","5.00","selfcert","sickness","rthtrhrthtrh"
    "12","1","2009-11-03","2009-11-12","9.00","selfcert","sickness","theasd"
    "13","1","2008-12-17","2008-12-18","1.00","selfcert","sickness","2654yt5"
    "14","344","2010-02-17","2010-02-18","2.00","selfcert","sickness","rthrthrt"

    the staff table you only need to know they have fullname and id.


    Usig HAVING as it's a group by clause - for use with SUM(). As far as I know that's the only way it could work.

    Cheers,
    T

  7. #7

    Join Date
    May 2009
    Location
    Leeds
    Posts
    22
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    11
    Sorted it. Went the temporary tables route in the end:
    Code:
    CREATE TEMPORARY TABLE temp_occ (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a), KEY(id)) select `id`, `userid`, `datefrom`, `dateto`, `numberdays` from occasions WHERE DATE_SUB(curdate(),INTERVAL 1 YEAR) <= `datefrom`;
    SELECT s.fullname AS userfullname,SUM(o.numberdays) AS daystotal, 
    o.datefrom from temp_occ o INNER JOIN tracking.staff s ON o.userid=s.id GROUP BY 
    o.userid
    The temporary table creation pulls out the correct date range, then all I need to do is SUM() the dates.

    Thanks for the input, it helped guide me in the right direction.

    T

  8. #8

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    :O) glad it's sorted

  9. #9

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    247
    Thank Post
    23
    Thanked 37 Times in 28 Posts
    Rep Power
    20
    Quote Originally Posted by Tim_S View Post
    Good questions.


    Usig HAVING as it's a group by clause - for use with SUM(). As far as I know that's the only way it could work.

    Cheers,
    T
    You can use WHERE as well you know! It goes after the FROM clause, and before the GROUP BY clause

    e.g.

    SELECT aggregated_absence_data
    FROM staff_absences_list
    WHERE absence_date > last_year
    GROUP BY staff_id
    HAVING absencecount > 3

    if you get my drift.

    You use the WHERE clause to restrict the data before it gets near the aggregate functions. That said, it's a few years since I used MySQL so it may be an MS-Only syntax. Don't we love those.

SHARE:
+ Post New Thread

Similar Threads

  1. Dates needed please
    By Dos_Box in forum General Chat
    Replies: 14
    Last Post: 8th February 2010, 08:02 PM
  2. SIMS Dates out
    By _techie_ in forum MIS Systems
    Replies: 2
    Last Post: 30th November 2009, 10:33 AM
  3. Excel sum problem
    By wesleyw in forum How do you do....it?
    Replies: 8
    Last Post: 29th August 2009, 11:23 AM
  4. Numerical Dates in SIMS
    By adamf in forum MIS Systems
    Replies: 6
    Last Post: 19th January 2009, 02:21 PM
  5. create a folder with dates,
    By sharkster in forum Scripts
    Replies: 2
    Last Post: 3rd April 2008, 09:04 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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