Mysql query working out percentages

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Mysql query working out percentages

Post 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
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql query working out percentages

Post 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.
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: Mysql query working out percentages

Post 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?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql query working out percentages

Post 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
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql query working out percentages

Post 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 =]
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql query working out percentages

Post 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);
Post Reply