SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query to display 4th option (not specified)

    I know the summary is pretty vague, but here's the situation. I'm getting stats from a table of registrants for a regional event. Almost all registrants will be from 1 of 3 provinces but the occasional one might be from somewhere else. I want to count how many from somewhere else (doesn't matter where). The field holds the 2 letter abbreviation of the province.
    The pseudo query would be "Select a count of people who didn't come from PE or NS or NB". Any help ?

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,066
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    SELECT COUNT(*) AS 'OtherProvinces' FROM table WHERE Province NOT IN ('PE', 'NS', 'NB')
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind I think I got it.
    SELECT COUNT(`reg_id`) FROM `registrants` WHERE `province` <> 'PE' AND `province` <> 'NB' AND `province` <> 'NS'

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    SELECT COUNT(*) AS 'OtherProvinces' FROM table WHERE Province NOT IN ('PE', 'NS', 'NB')
    I saw your post after I posted my solution (you were very quick!) It's a lot more elegant than mine but it doesn't seem to work. I subbed in the field to count and the name of the table, I assumed you meant Province to stand for the name of the field. Am I missing something.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,066
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    What database are you using? I'll assume MySQL, as mine was based on MSSQL.


    SELECT COUNT(`reg_id`) AS `OtherProvinces` FROM `registrants` WHERE `province` NOT IN ('PE', 'NB', 'NS')
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I'm using MySQL, and that query worked, thanks a lot it looks like what I had but I must have missed something.

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,066
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bcalagoure View Post
    Yes I'm using MySQL, and that query worked, thanks a lot it looks like what I had but I must have missed something.
    Yeah, MySQL doesn't like single quotes around column names (so once I changed it to `, it was happy).
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •