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
Mysql query working out percentages
Moderator: General Moderators
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: Mysql query working out percentages
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";
*/
Re: Mysql query working out percentages
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?
I would still have to work out the individual percentages after that?
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: Mysql query working out percentages
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
-Andy
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: Mysql query working out percentages
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 =]
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: Mysql query working out percentages
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);