Solved

SQL calculation

Posted on 2014-01-21
6
490 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 41

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 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 41

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now