SQL calculation

I need help working out how to do a calculation from my DB to populate a table.

I have a DB table set up to show the following:

Size          Stock              Sold
10            56                   24
10            100                 34
12            55                   40
12            33                   5
14            20                   12

I have been able to query the DB to calculate how many of each size I have in stock and how many are sold to output something like below in php:

Size         Stock               Sold
10            156                 58
12            88                   45
14            20                   12

However I need to add a 4th column which will tell me what % of stock has been sold for each size on the output table.

I have got really stuck - I have attached my code below, if anyone can help me.

thanks

   /* query Jan_View table */
  $result = mysqli_query($con,"Select
  Jan_View.Size,
  Sum(Jan_View.Stock),
  Sum(Jan_View.Sold)
From
  Jan_View
Group By
  Jan_View.Size");
  
  
    /* Loop through rows and display results */
  


echo "<table border='0'>
<tr>

<th>Size</th>
<th>Stock</th>
<th>Sold</th>
<th>Percentage Sold</th>
</tr>";
 
while($row = mysqli_fetch_array($result))

 
   {
  echo "<tr>";
  
  echo "<td>" .$row['Size'] . "</td>";
  echo "<td>" . $row['Sum(Jan_View.Stock)'] . "</td>";
  echo "<td>" . $row['Sum(Jan_View.Sold)'] . "</td>";
  echo "</tr>";
  }
echo "</table>"; 

Open in new window

eezar21Asked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
Try this:
   /* query Jan_View table */
  $result = mysqli_query($con,"Select
  Jan_View.Size,
  Sum(Jan_View.Stock),
  Sum(Jan_View.Sold),
  Sum(Jan_View.Sold)/Sum(Jan_View.Stock)*100 AS Percentage
From
  Jan_View
Group By
  Jan_View.Size");
  
  
    /* Loop through rows and display results */
  


echo "<table border='0'>
<tr>

<th>Size</th>
<th>Stock</th>
<th>Sold</th>
<th>Percentage Sold</th>
</tr>";
 
while($row = mysqli_fetch_array($result))

 
   {
  echo "<tr>";
  
  echo "<td>" .$row['Size'] . "</td>";
  echo "<td>" . $row['Sum(Jan_View.Stock)'] . "</td>";
  echo "<td>" . $row['Sum(Jan_View.Sold)'] . "</td>";
  echo "<td>" . $row['Percentage'] . "</td>";
  echo "</tr>";
  }
echo "</table>"; 

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
should be simple sql math:
Select
  Jan_View.Size,
  Sum(Jan_View.Stock),
  Sum(Jan_View.Sold),
  100 * Sum(Jan_View.Sold) / Sum(Jan_View.Stock) percent_sold 
From
  Jan_View
Group By
  Jan_View.Size

Open in new window


hope this helps
0
 
eezar21Author Commented:
Perfect - Simple solution, but now I know!!!

Thanks!!
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
eezar21Author Commented:
How can I round the output number to the nearest whole number - the output has 10 decimal places!??
0
 
pcelbaCommented:
You may use ROUND() function:

ROUND(Sum(Jan_View.Sold)/Sum(Jan_View.Stock)*100) AS Percent

More info: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

You should also test the Stock value for zero if necessary.
0
 
eezar21Author Commented:
Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.