Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    10

    Unanswered: Average type query

    Hello peoples i got a question . I havent been working alot with SQL queries and the like so please bear with me cause im slowly learning.

    Anyhow i got a table that looks like this
    date(date), time(TIME), name(varchar15), activity (int4).

    Just to be clear 'activity' is the time a person spent on an activity in minutes.

    The thing i want to achieven in a query is the following formula:
    Activity = (1xaverage activity +3xaverage activity from yesterday + 2x average activity the day before yesterday) /4.6

    average activity is calculated by the following:
    Average activity = (MAX(activity) + MIN(activity)) /2, I have reasons to calculate it like this .

    Anyhow im a bit stuck on how to do this. The parts i got so far is to calculate the average activity per day in a seperate query like this:
    SELECT date, (MAX(activity) + MIN(activity)) /2 as avActivity from tabel group by DAY(date);

    however i really dont know how to proceed further, as said eariler im a bit stuck. So if anyone has a good idea on how to go further thansk in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll need two self-joins, one to get yesterday and one for the day before yesterday

    what happens if these don't exist? what is the formula if yesterday or the day before yesterday is missing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    10
    Quote Originally Posted by r937 View Post
    you'll need two self-joins, one to get yesterday and one for the day before yesterday

    what happens if these don't exist? what is the formula if yesterday or the day before yesterday is missing?
    Ah joins, havent been that far yet in the learning proces, ill look em up now thanks alot .

    Ah and btw yesterday and the day before yesterday will ALWAYS exist. Mainly the query is being run from a php form where u can select from a dropdown the date. I already made it that the 'lowest date' that is selectable is the 3rd one in the table so that there is always a day before yesterday, and a day before

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume $dateparameter as a value like '2010-08-10'
    Code:
    SELECT $dateparameter AS thedate
         , (   today.avActivity 
           yesterday.avActivity * 3 +
           daybefore.avActivity * 2 ) / 4.6    AS Activity
      FROM ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity 
               FROM daTable 
              WHERE `date` = $dateparameter ) AS today
    CROSS
      JOIN ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity 
               FROM daTable 
              WHERE `date` = $dateparameter - INTERVAL 1 DAY ) AS yesterday
    CROSS
      JOIN ( SELECT (MAX(activity) + MIN(activity)) / 2 AS avActivity 
               FROM daTable 
              WHERE `date` = $dateparameter - INTERVAL 2 DAY ) AS daybefore
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    10
    Thanks alot sorry for late reply but ive been trying out to figure out the errors the query gave myself. Yours worked perfectly with the 1 exception :P

    WHERE `date` = $dateparameter - INTERVAL 2 DAY ) AS daybefore
    If i put the quotes around 'date' it doesnt work. Took me some time to figure out to remove em. After i did that it worked perfectly.

    Thanks alot already. Now im trying to figure out how to do it for mulitple days but no succes in that yet :P. With multiple days. I could just run a loop in PHP but trying to do it in mysql :P
    Last edited by bleastan; 08-12-10 at 09:58.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bleastan View Post
    WHERE `date` = $dateparameter - INTERVAL 2 DAY ) AS daybefore
    If i put the quotes around 'date' it doesnt work.
    do you understand the syntactic difference between quotes and backticks?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2010
    Posts
    10
    Quote Originally Posted by r937 View Post
    do you understand the syntactic difference between quotes and backticks?
    to be honest i cant say i do (or did). Just looked it up and apparently back ticks should work, but i kept getting errors and when i removed em it seemed to work :P

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assuming the column name is date,

    WHERE `date` = ... will always work

    WHERE 'date' = ... will never work

    WHERE date = ... might work, but since DATE is a reserved word, might not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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