SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a complex php mysql query...

    I have a mysql table eg:

    date name value
    01/01/12 a 1.12345
    01/01/12 b 1.7893
    02/01/12 a 1.13456
    02/01/12 b 1.6789

    I normally have to calculate to get a derived value for a certain date eg (1 * b.value/a.value)

    I am trying to get the average value AVG(value) of derived values for the entire month eg jan 2012.

    How can I put this into a single query within my php script?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code sql:
    SELECT
       AVG(a.VALUE / b.VALUE)
    FROM
       mytable a
       INNER JOIN
       mytable b
        ON t1.DATE=t2.DATE
       AND a.name = 'a'
       AND b.name = 'b'
    WHERE
       MONTH(DATE) = 1
       AND
       YEAR(DATE) = 2012

    (doesn't work if the date column isn't of type date, which given from your sample data I'm not sure it does)
    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 Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! It is indeed a date column type
    So how do I now get that value from that query?

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also I'm unclear on 2 things:

    t1. DATE and t2.DATE

    What is the the t1 and t2? Should that not be a.DATE and b.DATE?

    Also, the MONTH(DATE) etc, should that specified as MONTH(a.DATE)?

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by skyline View Post
    What is the the t1 and t2? Should that not be a.DATE and b.DATE?
    Ehm, yes. I changed my mind about the alias of that table half way through the query and forgot to amend it
    Indeed, those should be a and b

    Quote Originally Posted by skyline View Post
    Also, the MONTH(DATE) etc, should that specified as MONTH(a.DATE)?
    Either a.date or b.date; because you're JOINing on the date it doesn't really matter which one you pick, both work.
    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

  6. #6
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, had just figured it out as you posted All sorted ta!


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
  •