October 2, 2007 at 8:35 am
I am writing a system for tallying votes for an election and coming up utterly brain dead, too many details to tend to. I need to count how many polling places have reported. Here's the table that I'm querying:
CREATE TABLE [Votes] (
[PollingPlace] [tinyint] NOT NULL ,
[DisplayOrder] [tinyint] NOT NULL ,
[DrawnOrder] [tinyint] NOT NULL ,
[VoteCount] [int] NULL)
Now this requires a little bit of explanation. PollingPlace is a number from 1-28, these are the stations throughout town where people vote. The DisplayOrder field is effectively the race number: 0 for mayor, 1 for district 1, etc. Drawn order is the order the candidates appear on the ballots, drawn by lot.
I have three tables: candidates, votes, polling places. The candidate table has four fields: DisplayOrder, DrawnOrder, OfficeName, CandidateName (could be normalized further, but I'm preferring KISS due to time constraints). The PollingPlace table is largely irrelevant, it's just the polling place number, name of location, address: the number is all that's important, the rest is just for reporting or display. So to simplify input, I'm pre-populating the vote table with zero vote count records, then I've written an Access front end that brings up a screen, by polling place, that allows input for only the races that the residents of that particular polling area are allowed to vote in. So the entry operator isn't actually entering results, she's updating them from zero counts.
Got that? :hehe:
The end result is that the vote table, populated with a three section union query, will have 263 records (Candidates * polling places that can vote for them). So the mayor's race (5 candidates) will have 140 records (28 polling places).
Now on to the problem! I need to count how many polling places have reported. I started with this query:
select sum(votecount), pollingplace
from votes
group by pollingplace
having sum(votecount) > 0
and am stuck, this query is just not doing it for me. What I want is a single record showing how many polling places have reported. What I'm getting with this query is a row for each polling place when I need a count of the rows.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 2, 2007 at 8:47 am
Maybe this will do what you want?
select COUNT(DISTINCT pollingplace)
from votes
WHERE votecount > 0
It will give you all places that have reported at least something... not all that have reportes all results. For that we would need some additional info, about what "all results" means.
October 2, 2007 at 8:55 am
Vladan (10/2/2007)
Maybe this will do what you want?select COUNT(DISTINCT pollingplace)
from votes
WHERE votecount > 0
Bingo! Does the trick exactly. I've never seen or used a Distinct within a Count, that's a new trick for me that I'll have to remember.
The way the process works is that once the summary has been printed from the vote tabulators for any given polling place, they're written on to a sheet by the City Clerk then passed to the entry person, so it's an all or nothing proposition when it comes to recording the votes.
Thanks very much, V!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 2, 2007 at 11:07 am
Now that's the most brutally honest topic title I've ever seen.
You should get a price for that :).
October 2, 2007 at 11:14 am
Ninja's_RGR'us (10/2/2007)
Now that's the most brutally honest topic title I've ever seen.You should get a price for that :).
I pride myself on my self-deprecation and humility. 😛
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 2, 2007 at 11:18 am
Humility or humiliation ? :hehe:.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply