Page 1 of 1
Mysql query working out percentages
Posted: Thu Sep 04, 2008 8:39 pm
by recci
Hi
Say I have table called ratings, This table contains 20 fields called title1 through to title20. These title fields contain standard integer numbers. I want to add all the numbers from each field together to get a total number then work out what percentage of the total each of the individual title fields are.
How would I structure this in a mysql query? I could work out the percentages in PHP easily but as Iv been informed it would be more efficent to do this in the sql query and would result in a faster running script?
all advice welcome
cheers
Re: Mysql query working out percentages
Posted: Thu Sep 04, 2008 9:00 pm
by andyhoneycutt
Code: Select all
$query_sum = "";
// yields title1 + title2 + title3 ...
for($i=1;$i<21;$i++)
{
$query_sum .= "title$i + ";
}
// trim the string for (space) and + and (space)
$query_sum = rtrim(' + ',$query_sum);
// form the actual query...
$query = "SELECT ($query_sum) as total_ratings FROM ratings";
/**
* output for $query will be:
* "SELECT (title1 + title2 + title3 ... + title20) as total_ratings FROM ratings";
*/
This should cover what you're looking for.
Re: Mysql query working out percentages
Posted: Thu Sep 04, 2008 9:15 pm
by recci
Thanks but I dont quite understand that. Its just giving me the overall total?
I would still have to work out the individual percentages after that?
Re: Mysql query working out percentages
Posted: Fri Sep 05, 2008 12:57 am
by andyhoneycutt
What that statement does is add up all of those columns. if you want, divide it by 100 or however you want to derive your percentage- it will come from that result.
-Andy
Re: Mysql query working out percentages
Posted: Fri Sep 05, 2008 10:36 am
by andyhoneycutt
I missed the part where you want to find out what percentage of the total each item is, sorry. I'll get back to you shortly =]
Re: Mysql query working out percentages
Posted: Fri Sep 05, 2008 11:17 am
by andyhoneycutt
I am not sure how to efficiently do this in one query, so here it is using two queries:
Code: Select all
$dblink = mysql_connect("127.0.0.1","test","test");
mysql_select_db("test",$dblink);
$query_cols = "";
$query_sum = "";
// yields title1 + title2 + title3 ...
for($i=1;$i<21;$i++)
{
$query_sum .= "title$i + ";
}
// trim the string for (space) and + and (space)
$query_sum = rtrim($query_sum,' + ');
// form the actual query...
$query = "SELECT ($query_sum) as total_ratings FROM ratings";
$result = mysql_query($query,$dblink);
$row = mysql_fetch_assoc($result);
$total_ratings = $row['total_ratings'];
for($i=1;$i<21;$i++)
{
$query_cols .= "(title$i / $total_ratings * 100) as title$i, ";
}
// trim the string for comma space
$query_cols = rtrim($query_cols,', ');
$query = "SELECT $query_cols FROM ratings";
$result = mysql_query($query,$dblink);
$row = mysql_fetch_assoc($result);