?
Solved

SQL calculation

Posted on 2014-01-21
6
Medium Priority
?
541 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 43

Accepted Solution

by:
pcelba earned 1400 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 600 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

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 43

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 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