Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL calculation

Posted on 2014-01-21
6
Medium Priority
?
538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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 …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

610 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