CODEWITHSUNDEEP
mysqlsqlselect
james
james

Reputation:

Get percentage average

I'm unsure how to go about this either because it late, or I'm a MySQL noob (probably both to be honest).

I have a table called 'poll_votes' which comprises of: 

ID, poll_id, answer_id, ip, time_posted

What I need to do, is for each unique answer_id in a specific poll_id, calculate the percentage out of the total number of answers that the answer_id counts for. I'm not really good at wording what I need to do, so here is what the query should return (single row):

total -> total number of answers for this poll_id
[0] -> the percentage of 'total' that this makes up eg. 32 (%)
[1] -> the percentage of 'total' that this makes up eg. 16 (%)
[2] -> the percentage of 'total' that this makes up eg. 52 (%)

Obviously, all the percentages added together should equal one hundred. Also to note, that the field names for each percentage returned are not necessarily in any order.

I mean even if the query just returned the total and the number of votes for each answer_id, I'd be able to calculate it easily in PHP.

Could anyone give me any pointers?

Edit: This is what I have so far, but it doesn't work:

SELECT (SELECT COUNT(*) FROM poll_votes) AS total, answer_id, COUNT(answer_id) / total * 100 AS percentage GROUP BY answer_id

Upvotes: 1

Views: 2503

Answers (3)

Alexey Sviridov
Alexey Sviridov

Reputation: 3510

Of course you can't use field alias as field values everywhere you want (i'm about your 'total' aslias). You can't use aliases even in 'group by' clause

So you must use some like this

select answer_id, count(*) / (select count(*) from poll_votes where poll_id = pv.poll_id)
from poll_votes pv
where pv.poll_id = _your_specific_poll_id_
group by answer_id

you can multiply by 100 second column to get "pure" persents.

Upvotes: 0

Mercer Traieste
Mercer Traieste

Reputation: 4678

Not a beatiful answer because of the inner select, but should work (used text instead of int for the answer field):

drop table if exists ANSWERS;

create table ANSWERS (
  ID int not null auto_increment,
  ANSWER text,
  primary key (ID)
);

insert into ANSWERS (ANSWER) values ("Alpha");
insert into ANSWERS (ANSWER) values ("Beta");
insert into ANSWERS (ANSWER) values ("Gamma");
insert into ANSWERS (ANSWER) values ("Gamma");
insert into ANSWERS (ANSWER) values ("Delta");
insert into ANSWERS (ANSWER) values ("Delta");
insert into ANSWERS (ANSWER) values ("Delta");

select 
    ANSWER,
    count(ANSWER) / (select count(*) from ANSWERS) * 100 AS PERCENTAGE
from ANSWERS
group by ANSWER
order by PERCENTAGE;

Test drive:

mysql -u root test < percentage-usage.sql
ANSWER  PERCENTAGE
Alpha   14.2857
Beta    14.2857
Gamma   28.5714
Delta   42.8571

Upvotes: 0

Richard
Richard

Reputation: 30628

The simple solution would be 

select answer_id, count(answer_id) as answercount
from poll_votes
group by answer_id

and use 

select count(ID) from poll_votes

beforehand to get the total number of rows

Upvotes: 1

Related Questions