calculating percentages php/mysql
HI:
I have three tables in MySQL - total sales, tuff sales, and float sales
tuff and float are types of sale.
Each table has columns year, month and sales.
The sum of sales for each type of sale = total. So for each month tuff sales + float sales = total sales.
I have a php query that reads the figures for each table in separate columns.
So if total sales for August 2012 were £120, and tuff sales = £100 and float = £20, then the php result would read:
2012 08 120 100 20
However is it possible for php to execute the SQL that presents the tuff and float sales as percentage of total?
So then the php result would read:
2012 08 120 83.3 16.7
I could write the percentage to the source table as a fixed calculation, and php would then read that column, but where is the fun in that?!
Thanks!
I have three tables in MySQL - total sales, tuff sales, and float sales
tuff and float are types of sale.
Each table has columns year, month and sales.
The sum of sales for each type of sale = total. So for each month tuff sales + float sales = total sales.
I have a php query that reads the figures for each table in separate columns.
So if total sales for August 2012 were £120, and tuff sales = £100 and float = £20, then the php result would read:
2012 08 120 100 20
However is it possible for php to execute the SQL that presents the tuff and float sales as percentage of total?
So then the php result would read:
2012 08 120 83.3 16.7
I could write the percentage to the source table as a fixed calculation, and php would then read that column, but where is the fun in that?!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The way I see it, you have two choices ...
1. Change the SQL to calculate and return the percentage
2. Add logic in PHP to calculate the percentage on the returned values.
To help too much further with either would require more details on the SQL/PHP script. But the match is simple enough ... either way you would need to consider a round() on the result.
1. Change the SQL to calculate and return the percentage
2. Add logic in PHP to calculate the percentage on the returned values.
To help too much further with either would require more details on the SQL/PHP script. But the match is simple enough ... either way you would need to consider a round() on the result.
ASKER
Thank you very much!
rounding can be an issue ... sometimes if you round two numbers in a % calculation, the total will not = 100%. If this is of concern, calculate the first % then subtract that from 100 to get the other %.
But basically just use SUM(field/100) as tuffsales_percentage
But as you're using 3 tables (???) then the sql will be considerably more complicated.
As you have the sales in one table then I don't see the need for another seperate table just to hold this value - you can achieve the result with a simple sql on your tuff sales table e.g.
select sum(tuff_total) from tuff_sales_table where tuff_date BETWEEN '2012-08-01' AND '2012-08-31'