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
