SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I do three counts in one query?

    I have currently three queries that I'm running to find three counts. They are supposed to be counting messages that are old, for today and for the future. I am wondering if there is a way to do the same thing in one query. Here are the queries:

    SELECT COUNT(*) FROM messages WHERE userId = 1 AND show_date < today
    SELECT COUNT(*) FROM messages WHERE userId = 1 AND show_date = today
    SELECT COUNT(*) FROM messages WHERE userId = 1 AND show_date > today

    *today is to represent today's date only, not literally in the query.

    I know I could group by the show_date and filter the results outside of MySQL, but I wanted to know if there was a better way.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(CASE WHEN show_date < CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS old
         , COUNT(CASE WHEN show_date = CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS today
         , COUNT(CASE WHEN show_date > CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS future
      FROM messages 
     WHERE userId = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow. Cool. I'll try that instead. You put a lot in that query that I haven't seen before, so clearly I have some reading to do. Thank you so much.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT COUNT(CASE WHEN show_date < CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS old
         , COUNT(CASE WHEN show_date = CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS today
         , COUNT(CASE WHEN show_date > CURRENT_DATE
                      THEN 'ok' ELSE NULL END) AS future
      FROM messages 
     WHERE userId = 1
    If show_date has an index then will it be used for this query? If not then it might be more efficient to use separate queries if the table is large.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    lemon, if show_date does ~not~ have an index, then doing three separate queries will require 3 times as long as my query, which will require only one pass of the rows

    on the other hand, if it ~does~ have an index, that index might get ignored anyway, because of the WHERE clause on userid

    but my query will still examine all the rows in only one pass, even if it's an index search on the userid index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    lemon, if show_date does ~not~ have an index, then doing three separate queries will require 3 times as long as my query, which will require only one pass of the rows

    on the other hand, if it ~does~ have an index, that index might get ignored anyway, because of the WHERE clause on userid

    but my query will still examine all the rows in only one pass, even if it's an index search on the userid index
    Thanks, I was only wondering whether there's any chance your query use an index - I thought if that were true then I gained some new secret knowledge about mysql


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
  •