I would make the select statement look like this
SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(Votes.VoteNumber) ,
count(select votenumber from Votes)
FROM Candidate LEFT OUTER JOIN Votes ON Candidate.CANDIDATENUMBER =
Votes.CANDIDATENUMBER
GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME
By doing this you are using less resources the SQL server. Usually whenever
you do any select statement the sql engine will go retrieve all of the rows
you specify, even in a count. This will cause the SQL enginge to get every
row in this table. I also remember hearing someone mention that with MySQL
it is generally not a good idea to do a query with an (*) in it because it
could produce speratic data. So by doing a count on a particular column this
would alleviate that problem.
Aaron Weiker
Programmer
CISP - Changing Internet Speed & Performance
Phone: 419.724.5351 [EMAIL PROTECTED]
Pager: 419.218.0013 http://www.cisp.cc
Cell: 419.304.0323 web search:
http://www.allthesites.com
-----Original Message-----
From: Larry Kim [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 05, 2001 4:55 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: SQL query problem with mysql.
hi,
i'm writing a book (wrox publishers) which uses mysql for the sample
database.
i seem to have encountered a problem with an SQL query.
its a simple voting application, with a candidate table, and a vote table:
create table candidate (
candidatenumber integer not null auto_increment,
firstname varchar(32) not null,
lastname varchar(32) not null,
politicalparty varchar(32) not null,
primary key(candidatenumber));
create table votes (
votenumber integer not null auto_increment,
candidatenumber integer,
countynumber integer,
primary key(votenumber),
foreign key(candidatenumber) references candidate,
foreign key(countynumber) references county);
i want to do a query that shows firstname, lastname, the number of votes for
that guy, and the total number of votes cast as illustrated:
George, Bush, 2, 10
Al, Gore, 2, 10
Pat, Buchannan, 1, 10
Ralph, Nader, 5, 10
for example ralph nader received 5 votes out of a total of 10 cast.
Al gore received 2 votes out of 10 ... you get the idea.
here is my query:
SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(Votes.VoteNumber) ,
count(select * from Votes)
FROM Candidate LEFT OUTER JOIN Votes ON Candidate.CANDIDATENUMBER =
Votes.CANDIDATENUMBER
GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME
everything works except for the nasty count(select (*) from votes) which
seems to work on other db's.
if i take it away then it works fine. any ideas on how i could obtain a
count of the number of votes
cast?
thank you for your consideration.
Larry Kim
[EMAIL PROTECTED]
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
