Counting votes

Hello, all. We have a student election application where we need to
determine:
- Total votes for each candidate in each category
- Total votes for all candiates for that same category
Here are the tables: tblVotes holds the students' votes; tblNominees holds
the candidates for the various offices; tblValidNomineeCategories which
holds the variouses offices they can run for. One person may be running for
more than one office. Some candidates are write-ins, so they won't show up
in the tblNominees; we want to get an aggregate count of them for each
voting category. We've thought of using a cursor to loop through, but there
must a better way.
Here's the DDL of the three tables,
CREATE TABLE [tblVotes] (
[personID] [int] NOT NULL ,
[nomineeID] [int] NOT NULL ,
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeID],
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblNominees] (
[personID] [int] NOT NULL , --> ID of student voter
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
DEFAULT (getdate()),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeCategoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
(
[personID]
) REFERENCES [tblGeneralPerson] (
[personID]
),
CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
(
[nomineeCategoryID]
) REFERENCES [tblValidNomineeCategory] (
[nomineeCategoryID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblValidNomineeCategory] (
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[displayOrder] [smallint] NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT
[DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
(
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's some sample data in each table,
tblVotes:
personID nomineeID nomineeCategoryID isWriteIn dateAdded
----
4 36 STBPR 0
2/25/2005 5:01:00 PM
tblNominees:
personID nomineeCategoryID platformFilename isActive dateAdded
dateLastMod
----
--
36 STBPR platform_2.doc 1
2/4/2005 2/4/2005 11:22:00 AM
tblValidNomineeCategory:
nomineeCategoryID nomineeCategoryDesc displayOrder isActive
dateAdded
----
--
STBPR Student Body President 10 1
1/24/2005 11:38:00 AMI think I've got a start on this,
select a.nomineeid, count(a.nomineeid) [countPer],
(select count(*) from tblvotes c where c.nomineecategoryid = 'STBPR')
[countAll]
from dbo.tblvotes a join dbo.tblnominees b on a.nomineeid = b.personid
where a.nomineecategoryid = 'STBPR'
group by a.nomineeid
This returns,
nomineeID countPer countAll
36 3 4
37 1 4
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:OiXjSURHFHA.2616@.tk2msftngp13.phx.gbl...
> Hello, all. We have a student election application where we need to
> determine:
> - Total votes for each candidate in each category
> - Total votes for all candiates for that same category
> Here are the tables: tblVotes holds the students' votes; tblNominees holds
> the candidates for the various offices; tblValidNomineeCategories which
> holds the variouses offices they can run for. One person may be running
> for more than one office. Some candidates are write-ins, so they won't
> show up in the tblNominees; we want to get an aggregate count of them for
> each voting category. We've thought of using a cursor to loop through, but
> there must a better way.
> Here's the DDL of the three tables,
> CREATE TABLE [tblVotes] (
> [personID] [int] NOT NULL ,
> [nomineeID] [int] NOT NULL ,
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeID],
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblNominees] (
> [personID] [int] NOT NULL , --> ID of student voter
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
> DEFAULT (getdate()),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeCategoryID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
> (
> [personID]
> ) REFERENCES [tblGeneralPerson] (
> [personID]
> ),
> CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
> (
> [nomineeCategoryID]
> ) REFERENCES [tblValidNomineeCategory] (
> [nomineeCategoryID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblValidNomineeCategory] (
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [displayOrder] [smallint] NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
> DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT
> [DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
> CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
> (
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Here's some sample data in each table,
> tblVotes:
> personID nomineeID nomineeCategoryID isWriteIn dateAdded
> ----
> 4 36 STBPR 0 2/25/2005
> 5:01:00 PM
> tblNominees:
> personID nomineeCategoryID platformFilename isActive dateAdded
> dateLastMod
> ----
--
> 36 STBPR platform_2.doc 1
> 2/4/2005 2/4/2005 11:22:00 AM
> tblValidNomineeCategory:
> nomineeCategoryID nomineeCategoryDesc displayOrder isActive
> dateAdded
> ----
--
> STBPR Student Body President 10 1
> 1/24/2005 11:38:00 AM
>|||>>Some candidates are write-ins, so they won't show up in the
Nominees;<<
Please stop using that silly tbl- prefix. But to the point, if you
allow write-ins, then by definition they are a nominee. This is an
attribute of a nominee and not of the ballot.
While I am grateful for the DDL, I think that you over did it a bit.
Never use bits or other asembly language things in SQL, we do not need
the audit trail columns that should be handled with a log tool, pick
clearer names for attributes, etc.
CREATE TABLE Ballots
(voter_student_id INTEGER NOT NULL
REFERENCES Students (student_id),
nominee_student_id INTEGER NOT NULL
REFERENCES Nominees (student_id),
office_id VARCHAR(5) NOT NULL
REFERENCES Offices(office_id),
PRIMARY KEY (student_id, nominee_id, office_id));
CREATE TABLE Nominees
(student_id INTEGER NOT NULL
REFERENCES Students (student_id),
office_id VARCHAR (5) NOT NULL,
platform_file_name VARCHAR (500) NULL,
candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
CHECK (is_writein IN ('W', 'N'))
PRIMARY KEY (nominee_student_id, office_id);
CREATE TABLE Offices
(office_id VARCHAR (5) NOT NULL PRIMARY KEY,
office_desc VARCHAR (95) NOT NULL,
officeholder_student_id INTEGER -- null means vacant
REFERENCES Students (student_id));
SELECT nominee_student_id, office_id, COUNT(*) AS votes
FROM Ballots AS B
GROUP BY nominee_student_id, office_id;
SELECT office_id, COUNT(*) AS votes
FROM Ballots
GROUP BY office_id;
You can combine them into one query:
SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
(SELECT COUNT(*) AS votes
FROM Ballots AS B2
WHERE B1.office_id = B2.office_id) AS total_cast
FROM Ballots AS B1
GROUP BY nominee_student_id, office_id;|||Thank you, Celko. Your guidance is much appreciated, along with the correct
DDL. Thanks :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Celko, I've got one followup question: Why are bit columns not a good idea
in SQL Server tables? Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Finding thats fairly easy task.:)
http://tinyurl.com/42evx
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
> Celko, I've got one followup question: Why are bit columns not a good idea
> in SQL Server tables? Thanks.
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
>|||Thanks, Roji. Very in-depth and informative. Does Celko have his own web
site?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Finding thats fairly easy task.:)
> http://tinyurl.com/42evx
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
>|||Thats more easy :)
http://www.celko.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks, Roji. Very in-depth and informative. Does Celko have his own web
> site?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Roji :)
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23E4xr4aHFHA.3572@.TK2MSFTNGP14.phx.gbl...
> Thats more easy :)
> http://www.celko.com/
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
>