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 :)?
SELECT COUNT(*) AS ‘OtherProvinces’ FROM table WHERE Province NOT IN (‘PE’, ‘NS’, ‘NB’)
Never mind I think I got it.
SELECT COUNT(reg_id
) FROM registrants
WHERE province
<> ‘PE’ AND province
<> ‘NB’ AND province
<> ‘NS’
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.
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’)
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).