Getting a total of a COUNT column 1

[Foamcow]

I am building a simple poll system and part of this is a query to pull the poll results from a MySQL table.

The system contains 3 tables.
pollquestions - Contains just an id and question string.

polloptions - stores options for each question. contains id, questionid and a string for the option

pollanswers - contains the 'votes' cast in each poll. Contains and id, question id and option id.

To get the results data for a poll I'm using the following simple query which seems to work OK. (in this case I am grabbing the results for poll '2')

SELECT 
COUNT(*) as votes, 
polloptions.id,
polloptions.option 
FROM pollanswers 
LEFT JOIN polloptions ON polloptions.id=pollanswers.answerid 
WHERE pollanswers.questionid=2
GROUP BY pollanswers.answerid

This is fine and returns what I need, but if possible I would also like to be able to add up the total number of votes cast. i.e. The sum of the 'votes' column.

I'm having trouble doing this as if I try to use SUM(votes) the column isn't recognised (I guess because it's an alias) and if I use SUM(COUNT(*)) I get an error about the Invalid use of GROUP function.

I know this has to be simple!

BTW, yes I could do this via PHP but I am trying to improve my knowledge of MySQL so this would be nice to know.

Many thanks.

Incidentally, I am using MySQL 4

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[r937]

change your GROUP BY clause to this --

GROUP BY polloptions.id,polloptions.option WITH ROLLUP

oh, wait a sec -- which version? 4.0 or 4.1?


r937.com | rudy.ca

 

[Foamcow]

My internal test server is running 4.0.18
But I can hook this up to an external test server which seems to be using 4.1.20

I'll try your suggestion anyway.

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[Foamcow]

Hmm, that's returned something that may be useful.

Originally an example result would be

votes      id      option
1          6       Blue
5          7       Grey
2          8       Pink

But now I get

votes      id      option
1          6       Blue
1          6       <NULL>
5          7       Grey
5          7       <NULL>
2          8       Pink
2          8       <NULL>
8                  <NULL>

So I guess that last row is the total votes?
But what are the other rows with nulls?

Is it possible to get the total without having the extra rows for each option?
[/code]

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[Foamcow]

Aha, if I use

GROUP BY polloptions.id WITH ROLLUP

It omits the null rows but gives me a total row at the end (albeit with the last 'option' repeated)



<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[r937]

try removing the id from the query

r937.com | rudy.ca

 

[Foamcow]

Looks good to me!
I realised that over lunch actually
Thanks very much - * for you.


And for a bonus point...

Is there a way to do this so instead of adding a row the query adds an additional column containing the total votes?






<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[r937]

> ... so instead of adding a row the query adds an additional column containing the total votes?

yes, there is

could you show your current query and a few sample rows from each table


r937.com | rudy.ca

 

[Foamcow]

I've managed to get the answer with help from gmmastros.

SELECT COUNT(*) as votes,
	polloptions.id,
	polloptions.option,
	(SELECT COUNT(*) FROM pollanswers WHERE questionid = 2)  as totalvotes
	FROM pollanswers
	LEFT JOIN polloptions
	ON polloptions.id=pollanswers.answerid
	WHERE  pollanswers.questionid=2
	GROUP BY pollanswers.answerid

I've hit another snag though.
If a particular option has no votes then it won't show in the results.
I am trying to work out if I can still use 1 query to show all the possible options for a poll regardless of whether they have been voted for.

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[r937]

you really should not be doing this, and i'm suprised that george would have let it slip past him --

SELECT polloptions.id, polloptions.option, ...
GROUP BY pollanswers.answerid

this is horribly invalid

this is why i wanted to understand your data

you're grouping on the wrong thing and the LEFT OUTER JOIN seems dubious

try switching your tables around, or using a RIGHT JOIN



r937.com | rudy.ca

 

[Foamcow]

Hmm, ok.

Out of interest I've just come up with

SELECT 
o.id,
o.option,
COUNT(a.answerid) as votes,
(SELECT COUNT(*) From pollanswers Where questionid = 1)  as totalvotes
FROM polloptions o 
LEFT JOIN pollanswers a 
ON a.answerid=o.id
WHERE o.questionid = 1
GROUP BY a.answerid

Which seems to work. At least it returns what I need.

Here's my table structure and a tiny amount of sample data

--
-- Table structure for table `pollanswers`
--

CREATE TABLE `pollanswers` (
  `id` tinyint(5) NOT NULL auto_increment,
  `questionid` tinyint(5) NOT NULL default '0',
  `answerid` tinyint(5) NOT NULL default '0',
  `date` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `questionid` (`questionid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `pollanswers`
--


/*!40000 ALTER TABLE `pollanswers` DISABLE KEYS */;
LOCK TABLES `pollanswers` WRITE;
INSERT INTO `pollanswers` VALUES (47,1,2,'2007-05-01 15:51:24'),(46,1,2,'2007-05-01 15:46:40'),(45,1,2,'2007-05-01 15:45:50'),(44,1,1,'2007-05-01 15:30:01');
UNLOCK TABLES;
/*!40000 ALTER TABLE `pollanswers` ENABLE KEYS */;

--
-- Table structure for table `polloptions`
--

CREATE TABLE `polloptions` (
  `id` tinyint(5) NOT NULL auto_increment,
  `questionid` tinyint(5) NOT NULL default '0',
  `option` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `questionid` (`questionid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `polloptions`
--


/*!40000 ALTER TABLE `polloptions` DISABLE KEYS */;
LOCK TABLES `polloptions` WRITE;
INSERT INTO `polloptions` VALUES (1,1,'Agree'),(2,1,'Disagree'),(3,1,'Don\'t Know');
UNLOCK TABLES;
/*!40000 ALTER TABLE `polloptions` ENABLE KEYS */;

--
-- Table structure for table `pollquestions`
--

CREATE TABLE `pollquestions` (
  `id` tinyint(5) NOT NULL auto_increment,
  `question` text collate latin1_general_ci NOT NULL,
  `published` enum('on','off') collate latin1_general_ci NOT NULL default 'off',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `pollquestions`
--


/*!40000 ALTER TABLE `pollquestions` DISABLE KEYS */;
LOCK TABLES `pollquestions` WRITE;
INSERT INTO `pollquestions` VALUES (1,'Age legislation is a valuable benefit to my business.','on','2007-04-30 14:43:00');
UNLOCK TABLES;

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[r937]

my point was about grouping

when you have a GROUP BY query, look at the SELECT clause -- each item in the SELECT clause can be either an aggregate or non-aggregate expression

so when you write

SELECT o.id, o.option, COUNT(a.answerid) ...

you have a mix of non-aggregate and aggregate expressions

RULE: every non-aggregate must be in the GROUP BY clause

so for the above SELECT clause, you should have

GROUP BY o.id, o.option

please let me know if this is still not making sense

r937.com | rudy.ca

 

[Foamcow]

It makes sense. Thanks again.

George put me straight already

Your help has been much appreciated.

For the record, this is what I ended up with....

	SELECT
	o.id,
	o.option,
	COUNT(a.answerid) as votes,
	(SELECT COUNT(*) From pollanswers Where questionid = 1)  as totalvotes
	FROM polloptions o
	LEFT JOIN pollanswers a
	ON a.answerid=o.id
	WHERE o.questionid = 1
	GROUP BY o.id, o.option
	ORDER BY votes DESC

Now, I'm assuming my ORDER BY isn't in the wrong place. Should it come before the GROUP BY?

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.