SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grab Rows by Different Dates w/ Single Query?

    Alright,

    Here's my conundrum. I've grabbed rows by date and date range before, but is it possible to do multiple COUNT in a single query.

    Like, I want to know how many rows have been added that day, that week, that month, and that year. And get that information all in a single query.

    Possible? And, if so, how resource intensive is it.

    Thanks for the help

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    possible, and not at all resource intensive if your date column has an index (for the WHERE clause)
    Code:
    SELECT COUNT(*) AS year_count
         , COUNT(CASE WHEN MONTH(somedate) = 
                           MONTH(CURRENT_DATE)
                      THEN 'curly' 
                      ELSE NULL END) AS month_count
         , COUNT(CASE WHEN WEEKOFYEAR(somedate) = 
                           WEEKOFYEAR(CURRENT_DATE)
                      THEN 'larry' 
                      ELSE NULL END) AS week_count
         , COUNT(CASE WHEN somedate = 
                           CURRENT_DATE
                      THEN 'moe' 
                      ELSE NULL END) AS day_count
      FROM daTable
     WHERE somedate >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
       AND somedate  < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
                                    + INTERVAL 1 YEAR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow. That is definitely different than the approach I went with.

    What you think of this part:

    PHP Code:
    SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),1,0)) AS videostoday
    SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK),1,0)) AS videosweek
    SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH),1,0)) AS videosmonth
    SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR),1,0)) AS videosyear 
    The above worked. But less efficient than yours?

    Cheers!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The use of CASE is SQL standard. The use of IF as you have done, will work in MySQL but won't if you switch to another database down the road.

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that update.


    In terms of efficiency (use of server resources), is one option better than the other?

    This query will likely run about 300K times per day. So need it to not be wasteful.

    Cheers!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by casbboy View Post
    In terms of efficiency (use of server resources), is one option better than the other?
    if you aren't using a WHERE clause, your query will do a table scan

    my query's WHERE clause allows an index on the date column to be utilized, to restrict the results to the current year only

    300K times per day??? you should consider designing a table to hold statistical results... no sense in counting the same things over and over
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, and by the way, your counts are all wrong

    subtracting INTERVAL 1 MONTH from today's date does ~not~ give you the current month, it gives you the previous 30/31 days

    if i read your first post, i think i got the right totals and you didn't

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

  8. #8
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are right.

    Dang. hmmm

    Okay, I've updated the where clause with the date range. can I just update my IF statements to grab current month and not last 30/31 days?

    SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH),1,0))

    would become...

    PHP Code:
    SELECT COUNT(trailers.trailer_id) AS videosyear
    SUM(IF(trailers.date >= CURRENT_DATE,1,0)) AS videostoday
    SUM(IF(WEEKOFYEAR(trailers.date) = WEEKOFYEAR(CURRENT_DATE),1,0)) AS videosweek
    SUM(IF(MONTH(trailers.date)= MONTH(CURRENT_DATE),1,0)) AS videosmonth 
    FROM trailers 
    WHERE trailers
    .date >= CURRENT_DATE INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
       
    AND trailers.date CURRENT_DATE INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
                                    
    INTERVAL 1 YEAR 
    Does that work? It certainly runs okay, and the WHERE clause makes sure there is no overlap on the MONTH WEEK and all that.

    Look good?

    Thanks so much!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by casbboy View Post
    ... and the WHERE clause makes sure there is no overlap on the MONTH WEEK and all that.
    um, no

    the WHERE clause merely restricts the rows to the current year

    Quote Originally Posted by casbboy View Post
    Look good?
    replace your IFs with CASE expressions and it's looking more and more like post #2 all the time...

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

  10. #10
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol. fine. I'll just use yours.

    I did, but I had to change:

    COUNT(CASE WHEN trailers.date =
    CURRENT_DATE
    THEN 'moe'
    ELSE NULL END) AS day_count

    to

    COUNT(CASE WHEN trailers.date >=
    CURRENT_DATE
    THEN 'moe'
    ELSE NULL END) AS day_count

    for it to acknowledge/count today's updates. (it was leaving it at 0)

    Cheers!
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ah, so you store a datetime value in a column called `date`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, indeedy

    Thanks again
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •