Count total from a column?

Hi,
I don't know if this is best solved in my SQL query, or via PHP.

Basically I have a table answers, which has 4 columns: answerid, questionid, answertext and votecount.

So votecount stores in how many times this answer has been voted for, and the question id is linked to a question in a question table.

Now, I wanted to list all answers to a speicfic question, so I can do this easily by:

$res = mysql_query("SELECT * FROM answers WHERE qid = '$qid'");
while($row = mysql_fetch_array($res)){
echo "Answer Text:".$row['answertext']."<br />";
//I want to work out percentage here but I need to divide by the total amount of votes
}

PHP:

What I need to know is how can I find the total number of votes, before I loop through the results. Will I need to run the query twice? Cause I need the total votes to work out the percentage. The total votes equals the sum of all of the votecounts returned from the query.

I know there are count functions in SQL and php but have never really used them. Can they return a total like I need.

Many thanks in advance.

 

if you need total votes use count, if you need calculate percents for example how much voted one star, two stars or other things.. you can use
SELECT COUNT(first),COUNT(second) FROM table WHERE question_id =

 

Thanks I actually needed the SUM function but you pointed me in the right direction.

Does this mean I need to run 2 queries?

Here are the 2 queries I am working which is working fine atm:

	$query = "SELECT * FROM answers WHERE qid = '$detail'";
	$query2 = "SELECT SUM(acount) FROM answers WHERE qid = '$detail'";
	$res = mysql_query($query) or die(mysql_error());	
	$res2 = mysql_query($query2) or die(mysql_error());

	$totVotes = mysql_fetch_array($res2);
	$totalVot = $rowww['SUM(acount)'];

	while($row = mysql_fetch_array($res)){
		$percentPerVote = 100/$totVot;
		$percent = round($row['acount']*$percentPerVote);
		echo " Answer Choice: ".$row['atxt']." Answer Votes: ".$row['acount']."Pecentage: $percent <br />";
	}

PHP:

The queries are basically doing the same thing but one is returning a sum of a column, can it be condesed into a single query, or is this the best.

 

1 query->
SELECT SUM(acount),acount,atxt FROM answers WHERE qid = '$detail'

 

Thanks and sorry to see you have got banned..

The problem with this is that it seems to only be able to return 1 row, or that's all I can do. Because:
SELECT SUM(acount),acount,atxt FROM answers WHERE qid = '$detail'
Should be returning the SUM which is 16, which it is doing, but also 3 answers, but it's only returning the 1st answer .

For example here's what i'm trying:

$res = mysql_query("SELECT SUM(acount),acount,atxt FROM answers WHERE qid = '$detail'");
while($row = mysql_fetch_array($res)){
$percentPerVote = 100/$row['SUM(acount)'];
$percent = round($row['acount']*$percentPerVote);
echo "Answer: ".$row['atxt']."<br /> Percent: $percent";
}

PHP:

It should be returning 3 answers, but because the SUM is in there it only returns the first one or I am looping through it wrong..

If anybody else knows this please let me know, cause it seems neegeris has been banned.

Thanks in advance to any help.

 

It is impossible using only 1 SQL statement. You have two options: either you make double sql queries, or nested sql statements -still 1 query. The second part will be harder for your to create and others to debug. I suggest you stick with double sql queries.

 

Thanks,

By double sql queries do you mean use 2 SQL queries like I am already doing in post 3 of this topic.

I've got no problem using 2 queries, i'm just new to this and I know the fewer queries the less resources are used so it's better to understand how queries are combined.
Would using a nested query use less resources than 2 seperate queries?

Lastly, i've got my percentages working via the 2 queries. Now is it possible to show like an image of a bar filled up in relation to the percentage?

 

Show your DB schema for both tables and also the SQL that you're using. It's entirely possible to get what you need from one query (using SQL_CALC_FOUND_ROWS).

 

Hi,

This is the PHP/SQL I am using, I have tried to comment it out and cut it down as far as possible, all it is doing, is getting the question from the question table and displaying it, and then displaying all answers which are linked to this question, their total votes, and percentage:

//Let's check if somebody wants to view a Question in detail
if($_GET['detail']){

	$detail = $_GET['detail'];
	
	//Retrieve the question
	$query = "SELECT * FROM questions WHERE qid = '$detail'";
	//Retrieve all answers that are linked to this question
	$query2 = "SELECT * FROM answers WHERE qid = '$detail'";
	//Retrieve the total amount of votes cast, for use in percentage calculation
	$query3 = "SELECT SUM(acount) FROM answers WHERE qid = '$detail'";
	
	//Execute the 3 queries
	$result = mysql_query($query) or die(mysql_error());	
	$result2 = mysql_query($query2) or die(mysql_error());
	$result3 = mysql_query($query3) or die(mysql_error());
	
	//Store the question details in $row
	$row = mysql_fetch_array($result);
	
	//Store the total votes cast in $totalVotes
	$totalVotes = mysql_fetch_array($result3);
	
	//Output Question Text
	echo "Question Text: ".$row['qtxt']."<br />";
	
	//Store Total amount of votes cast
	$totVot = $totalVotes['SUM(acount)'];
	$percentPerVote = 100/$totVot;
	
	//Now lets loop through the answers in relation to the question, and display their percentage
	while($row2 = mysql_fetch_array($result2)){
		//Calculate percentage
		$percent = round($row2['acount']*$percentPerVote);
		//Output percentage
		echo "Answer Choice: ".$row2['atxt']." Answer Votes Cast: ".$row2['acount']."Pecentage Recieved: $percent <br />";
	}
}

PHP:

As you can see query2 and query3 are similar in nature, one just gets the total votes.

My 2 tables, questions and answers are as follows:

tablename: questions (3 columns)
columns: qid(primary, auto increment), qtxt(contains the question text), qdate(date question was added)

tablename: answers (4 columns)
columns: aid(primary, auto increment), qid(linked to question id), atxt(contains the answer text), acount(the number of times the answer has been voted for)

Obviously the answers are linked to a question through the qid column.

 

Try adding "GROUP BY qid" at the end of your query like this:

SELECT SUM(acount),acount,atxt FROM answers GROUP BY qid

Code (markup):

Brew

 

Hi,

Do you mean like this:

"SELECT SUM(acount), acount, atxt FROM answers WHERE qid = '$detail' GROUP BY qid"

This again returns the SUM but only 1 answer (the first one), where it should be returning a few.