bcalagoure — 2013-05-01T10:17:05-04:00 — #1
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 :)?
cpradio — 2013-05-01T10:23:46-04:00 — #2
SELECT COUNT(*) AS 'OtherProvinces' FROM table WHERE Province NOT IN ('PE', 'NS', 'NB')
bcalagoure — 2013-05-01T10:25:13-04:00 — #3
Never mind I think I got it.
province <> 'PE' AND
province <> 'NB' AND
province <> 'NS'
bcalagoure — 2013-05-01T10:30:25-04:00 — #4
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.
cpradio — 2013-05-01T10:34:47-04:00 — #5
What database are you using? I'll assume MySQL, as mine was based on MSSQL.
province NOT IN ('PE', 'NB', 'NS')
bcalagoure — 2013-05-01T10:43:04-04:00 — #6
Yes I'm using MySQL, and that query worked, thanks a lot it looks like what I had but I must have missed something.
cpradio — 2013-05-01T10:47:10-04:00 — #7
Yeah, MySQL doesn't like single quotes around column names (so once I changed it to `, it was happy).
bcalagoure — 2013-05-01T10:52:01-04:00 — #8