SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: Query and SUM

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query and SUM

    hi guys,

    Im after a little help with 2 parts of my project, hopefully its something im just completely missing the point on.

    Firstly, Im querying a table in the DB that has people and times basically, when the query is run it uses the sum feature to add up the entries for each person. This works fantastically except due to extra functionality needed ive had to add a new field to the table. So my sum now needs to take this field into account....

    For example:
    Times_table

    id-------name-------seconds--------rate
    1-------john---------10920----------L
    2-------sarah--------49520----------H
    3-------john---------81324----------L
    4-------john---------9200-----------H

    So i would want it to add Johns seconds together (id)1+3 but not id 4 as the rate is different.....

    Secondly, on the same table(there are more fields than the example above) there is a date field. At the moment i query the data for the required date and us PHPexcel to export it, and basically i get the above data (after the sum) written to a spreadsheet in a similar style to above. What i need though is to get that data over a date range (which i have done) but then split it into 7 day periods.

    The data needed is from the last Friday of last month to the last Friday of the current month, and split into weeks (Fri-Thurs).

    example:
    month range (friday to friday)
    id------name------seconds------week1-----week2------week3------week4

    Apologies if that doesn't make sense, hopefully it does...

    If someone could point me in the correct direction i would be great full.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    For the first part you just need to use GROUP BY name, rate to split it out to L/H

    As for the second part ... I would probably find out the dates of the two needed Fridays (start on day one of the month after the month you're looking for and go back day by day until you hit a Friday), request everything in between those two, and handle the rest in PHP.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply, cant believe i missed the first one, so easy when you know eh.....

    As for the second one im still a little confused....

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Hm, I just thought of an alternative to step 2, but it's a bit hacky. Should work though.

    1. Get the date of the last Friday of the previous month
    2. Get the date of the last Friday of this month
    3. Get the date of the intervals.

    So for this month it would be from 2011/05/27 until 2011/06/24, with in between 2011/06/03, 2011/06/10 and 2011/05/17.
    Okay, s

    Week 1 is from 2011/05/27 until 2011/06/03
    Week 2 is from 2011/06/04 until 2011/06/10
    Week 3 is from 2011/06/11 until 2011/05/17
    Week 4 is from 2011/05/18 until 2011/06/24

    So now you have 4 ranges you can add in your query:

    Code:
    SELECT 
       some
     , fields
     , date BETWEEN '2011-05-27' AND '2011-06-03' AS week1
     , date BETWEEN '2011-06-04' AND '2011-06-10' AS week2
     , date BETWEEN '2011-06-11' AND '2011-06-17' AS week3
     , date BETWEEN '2011-06-18' AND '2011-06-24' AS week4
    ...
    GROUP BY
       name, week1, week2, week3, week4, rate
    Like I said ... hacky

    PS. Beware that between the last Friday of last month and last Friday of this month are not necessarily 4 weeks; it may be five!
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That seems like a good approach, and to be honest although i didn't know how to go about it, it looks exactly how i want it. The 4/5 week issue is what was sticking out as a possible problem, not quite sure how to overcome this part.

    I know you said its hacky but im not to concerned as this website wont ever be on the public internet. Its an internal thing only.

    Is there some kind of php function that can pick out the dates of all the Fridays in a given date range? And can the sql query be built on the result of such a function? Something similar to a num_rows type of thing.

    Sorry for the non tech jargon, however i am still learning this PHP sql stuff.

    Thanks again for the response.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    Ive managed to get somewhere with the PHP side of things and created a script that will give me the correct dates for the corresponding month.

    I am however having a slight issue with the sql query suggested above.
    This is the code i have:

    Code SQL:
    $sql = "SELECT user_id, name, sum(hours_worked) AS sum_hours, location, sum(lunch) AS sum_lunch, sum(break) AS sum_break, sick, holiday, clock_status.pay_rate FROM clock_status , staff,
    amend_time BETWEEN '$period1' AS week1
    , amend_time BETWEEN '$period2' AS week2
    , amend_time BETWEEN '$period3' AS week3
    , amend_time BETWEEN '$period4' AS week4
    , amend_time BETWEEN '$period5' AS week5
    WHERE auth = 1 and clock_status.user_id = staff.staff_number
    and clock_status.location = '$ident'
    GROUP BY user_id, week1, week2, week3, week4, pay_rate "

    The $periodX echos as: 2011-06-24 AND 2011-06-30 etc....
    Code PHP:
    $period1 = $date_array[0]. " AND " .date('Y-m-d' , $newdate);

    and this is the error i get:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN '2011-05-27' AND '2011-06-02' AS week1 , amend_time BETWEEN '2011-06' at line 2

    I know its going to be something simple but i just cant see it. Any help would be greatly appreciated....

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Can you echo $sql and post that here?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Let's format that query a bit better:
    Code:
    $sql = "
      SELECT 
          user_id
        , name
        , sum(hours_worked) AS sum_hours
        , location, sum(lunch) AS sum_lunch
        , sum(break) AS sum_break
        , sick
        , holiday
        , clock_status.pay_rate 
      FROM clock_status , 
              staff,
              amend_time BETWEEN '$period1' AS week1
            , amend_time BETWEEN '$period2' AS week2
            , amend_time BETWEEN '$period3' AS week3
            , amend_time BETWEEN '$period4' AS week4
            , amend_time BETWEEN '$period5' AS week5
    WHERE auth = 1 
    and clock_status.user_id = staff.staff_number
    and clock_status.location = '$ident'
    GROUP BY user_id, week1, week2, week3, week4, pay_rate 
    ;"
    What is the part in red supposed to do?
    They aren't tables, so they shouldn't be in the FROM clause, that's for sure.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    umm, i cant seem to echo $sql.....

    As for the bits in red.....

    They are to select the data from the amend_time table in weekly intervals and store as an alias.

    What im hoping to get from this is to query the data for a monthly period and split into weeks. That data will then be exported to excel so each week will be in a column of its own.

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    The you should put them in the SELECT, not in the FROM
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, i knew it would be something obvious, i will be taking guido2004's formatting advice from now on. My query scrolled right across the screen so completely missed the FROM bit.

    Thanks again guys.... Now to make all the data export.....

  12. #12
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i knew it wouldnt be that straight forward....

    Originally this was a weekly export, so using PHPexcel i would have used the sum_hours to put the number of hours into row I on excel. Now this is a monthly thing how do i use the sum_hours and the week1/week2 etc....

    PHP Code:
    $objPHPExcel->getActiveSheet()->setCellValue('I' $row'='.$d['sum_hours'].'/86400'); 
    to show like this:

    PHP Code:
    $objPHPExcel->getActiveSheet()->setCellValue('I' $row'='.sum_hours by week1);
            
    $objPHPExcel->getActiveSheet()->setCellValue('J' $row'='.sum_hours by week2);
            
    $objPHPExcel->getActiveSheet()->setCellValue('K' $row'='.sum_hours by week3);
            
    $objPHPExcel->getActiveSheet()->setCellValue('L' $row'='.sum_hours by week4); 


Bookmarks

Posting Permissions

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