SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieve highest and lowest values for a field, in one query?

    Hi All,

    I have a table, something like the following:

    SiteStatistics
    -------------------
    SiteStatisticsID (PK)
    SiteID (FK)
    Month (Numerical 1-12)
    Year (4 Digit Numerical)
    Uniques

    I have a requirement, to extract the highest number of uniques, and the lowest number of uniques within the past twelve months...

    Is this possible in one query?

    Currently I have a for loop to loop 12 months, then im creating a date and querying the database, so just to get the highest and lowest i've executed 12 queries

    After i've done this, I query the database again to get the rows for the past twelve months with their data and use the highest and lowest figures I pulled out previously to calculate the graph..

    Any Ideas?
    Thanks
    Matt

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    You might be able to do something like this:

    Code:
    SELECT MAX(Uniques) as max_value,
           MIN(Uniques) as min_value
    FROM SiteStatistics
    WHERE CONCAT_WS('-', Year, Month) >=
      DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    matt, what version of mysql are you on?

    erik, your where clause is quite clever

    however, i think the problem is the equivalent of "max(sum(uniques))" except of course that you can't nest aggregates!!

    your query just gets the max and min for any single month over the past 12 months, whereas i'm guessing matt wants the min and max totals

    also, just a min and max by themselves doesn't seem to be all that useful if you don't know which site it was for, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    your query just gets the max and min for any single month over the past 12 months, whereas i'm guessing matt wants the min and max totals
    Ah, you're right (as usual).
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Eric, Rudy.

    mySQL Version: 3.23.56

    Im trying to get the lowest figure for uniques within the past 12 months, and the highest figure for uniques.

    So for example:

    Month Uniques
    ---------------------
    Jan 5000
    Feb 2000
    March 8000
    April 10000

    Should return 2000 as the lowest, and 10000 as the highest.

    Thanks,
    Matt.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ah, okay, my guess was wrong, use erik's query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I was right? Cool...
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  8. #8
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    matt-, you could make your life much easier if you'd use appropriate column type for dates (i.e. DATE)...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    interesting idea, stereofrog, and i don't totally disagree

    but consider that (it looks like) the stats are for a month, i.e. one row for january, another row for february, etc

    what date value would you use in your DATE column?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    interesting idea, stereofrog, and i don't totally disagree
    Thank you very much, sir.

    Quote Originally Posted by r937
    what date value would you use in your DATE column?
    The 1st of given month, for example.

  11. #11
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the comments stereofrog, maybe I should have used a proper date value, but I thought that overall it would be easier/cleaner to work this way - possibly the wrong decision but there you go

    Thanks for the help Rudy and thanks for the query Eric, works a treat

  12. #12
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Further to this, how can I make the following query order by last month at the top?

    Code:
        SELECT
            Year,
            Month,
            Uniques,
            PageViews
        FROM
            SiteStatistics
        WHERE
            SiteID = 1						
        ORDER BY
            Year, Month DESC
        LIMIT 12
    My attempt doesn't appear to work..im getting the following order:

    Month Year
    12 2004
    2 2005
    1 2005

    Where im trying to achieve

    Month Year
    2 2005
    1 2005
    12 2004

    Any Ideas?

    Thanks,
    Matt.

  13. #13
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Since you haven't specified the DESC keyword for the Year column, it sorts it in an ascending order.

    Code:
    ORDER BY Year DESC, Month DESC
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  14. #14
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, Thought it might be something like that, I didn't know that you could apply DESC for both though.

    Thanks very much,
    Matt.


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
  •