Solved

SQL calculation

Posted on 2014-01-21
6
517 Views
Last Modified: 2014-01-21
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

0
Comment
Question by:eezar21
  • 3
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 350 total points
ID: 39796566
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 39796572
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
 

Author Closing Comment

by:eezar21
ID: 39796627
Perfect - Simple solution, but now I know!!!

Thanks!!
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:eezar21
ID: 39796648
How can I round the output number to the nearest whole number - the output has 10 decimal places!??
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39796662
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
 

Author Comment

by:eezar21
ID: 39796670
Thanks again!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

828 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