[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


calculating percentages php/mysql

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-09-17
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?!
Question by:ClaytonGlass
LVL 58

Expert Comment

ID: 38405307
If we had the actual sql it would be easier to give an example
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'
LVL 35

Accepted Solution

gr8gonzo earned 1200 total points
ID: 38405310
I would just use PHP to do the percentage calculation. MySQL or PHP can do it, but I always try to keep the database calculations to a minimum - especially when the script can do it the exact same way.

$year = "2012";
$month = "08;
$total = 120;
$tuff = 100;
$float = 20;

echo "{$year} {$month}  {$total}  " . round(($tuff/$total)*100,1) . " " . round(($float/$total)*100,1);
LVL 25

Expert Comment

ID: 38405317
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.

Author Closing Comment

ID: 38405319
Thank you very much!
LVL 25

Expert Comment

ID: 38405356
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 %.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question