SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Combining 2 queries of the same table?

    Hi,

    I'm trying to combine 2 statements for a "private messages" feature. The first query gets the number of total messages in their "inbox" and the second query gets the number of messages that haven't been read yet. The two queries are as follows:

    SELECT count(*) FROM pms WHERE uid = 'uid'
    SELECT count(*) FROM pms WHERE uid = 'uid' AND read = '0'

    Ideally, I'd like to query the database all in one pass...and have both numbers contained in the same result. What I'm trying to do is get both numbers in the least amount of database access.

    Thanks,

    Cranjled

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select count(*) as messages
         , sum(
             case when read = 0
                  then 1 else 0 end
              ) as unread_messages
      from pms 
     where uid = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict cranjled's Avatar
    Join Date
    Apr 2004
    Location
    ny
    Posts
    382
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!


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
  •