SitePoint Sponsor |
|
User Tag List
Results 1 to 18 of 18
Thread: Percentage in mysql query
-
Dec 2, 2009, 09:08 #1
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Percentage in mysql query
Okay, so I am a newbie to php and mysql. I will try to explain what I am trying to accomplish as best I can. I have a table with the following fields: wireGauge, time, fracture and other fields not used in this query. The fracture field stores the fracture point each time a record is added. There are 3 types of fracture points: anchor bend, break back and weld. What I am trying to do is figure out the percentage for each fracture point for a given wireGauge (such as 3, there are 4 different wireGauges: 2, 3, 4 and 4.5). I also want the user to be able to select the range of time from which to generate this report. So if there are 20 records I want to write a query that figures out the percentage of anchor bends, the percentage of break backs and the percentage of welds between a chosen time period for a given wireGauge such as 3. Once I have written the query I also need to figure out how to display the percentages in a table using php.
I have attached a screenshot of my table.
So far I have written the following:
PHP Code:$sql="SELECT fracture, COUNT(*)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
GROUP BY fracture";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$bbcount0 = $row['fracture'];
$bbcount1 = $row['fracture'];
$bbcount2 = $row['fracture'];
}
HTML Code:<table> <tr> <td> <?php echo "$bbcount1"; ?> </td> <td> <?php echo "$bbcount0"; ?> </td> <td> <?php echo "$bbcount2"; ?> </td> </tr> </table>
-
Dec 2, 2009, 12:21 #2
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
You don't need to change your query, you have all the data you need. Add up the count from all the rows, and divide each row's individual count by that to get its percentage.
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 2, 2009, 12:37 #3
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks for the quick response. I actually just figured it out and got it working. I did a count of the total rows like you said and then divided my first query by the total rows.
I changed my query and variables slightly so they make more sense for the project and now it looks like this:
PHP Code:$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND fracture = 'Anchor Bend'
AND endType = 'Loop;";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$abcount3 = $row['COUNT(fracture)'];
}
PHP Code:$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$totalFracture3 = $row['COUNT(fracture)'];
}
HTML Code:<table> <tr> <td> <?php echo round(($abcount3/$totalFracture3) * 100) . "%"; ?> </td> <td> <?php echo round(($bbcount3/$totalFracture3) * 100) . "%"; ?> </td> <td> <?php echo round(($bbcount3/$totalFracture3) * 100) . "%"; ?> </td> </tr> </table>
-
Dec 2, 2009, 12:41 #4
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
The second query is unnecessary, you already have the data (the row count is the same as the sum of all the fractures' counts you already retrieved). Eventually stuff like that catches up to you when you can't afford to be wasteful with expensive queries.
Also, you don't need a while loop when you know you only have one row
PHP Code:while($row = mysql_fetch_assoc($result))
PHP Code:$row = mysql_fetch_assoc($result);
PHP Code:$totalFracture3 = mysql_result($result, 0);
Also, right now, you're making no distinction between the fracture types as you loop over the rows. You're displaying the same number 3 times instead of the 3 percentages.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 2, 2009, 13:05 #5
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I am not completely understanding how to print out that data that you say I have already retrieved, but am eager to learn how and understand this better. I am going to show you my complete code for the queries I am using because now I am sure that I am probably making more work out of it than I need to. Here is what I have for queries:
PHP Code:
//query of % that are anchor bend and wire gauge 3 with endType Loop
$conn = mysql_connect("localhost","user","pass") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("database", $conn) or trigger_error("SQL", E_USER_ERROR);
$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND fracture = 'Anchor Bend'
AND endType = 'Loop;";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$abcount3 = $row['COUNT(fracture)'];
}
//query of % that are break back and wire gauge 3 with endType Loop
$conn = mysql_connect("localhost","user","pass") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("database", $conn) or trigger_error("SQL", E_USER_ERROR);
$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND fracture = 'Break Back'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$bbcount3 = $row['COUNT(fracture)'];
}
//query of % that are weld and wire gauge 3 with endType Loop
$conn = mysql_connect("localhost","user","pass") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("database", $conn) or trigger_error("SQL", E_USER_ERROR);
$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND fracture = 'Weld'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$weldcount3 = $row['COUNT(fracture)'];
}
//query of total # rows that are wire gauge 3 with endType Loop
$conn = mysql_connect("localhost","user","pass") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("database", $conn) or trigger_error("SQL", E_USER_ERROR);
$sql="SELECT COUNT(fracture)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$totalFracture3 = $row['COUNT(fracture)'];
}
Anchor Bend, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End
Break Back, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End
Weld, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End
I thought I had to query each one separately and then divide it by the total rows that are Wire Gauge 3 with a Loop End. You mentioned not needing a while loop and that I am making no distinction between fracture types. How can I do this in a cleaner and easier way?
Thanks so much for your help.
-
Dec 2, 2009, 13:12 #6
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
You can get all that information in one query:
PHP Code:$conn = mysql_connect("localhost","user","pass") or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db("database", $conn) or trigger_error("SQL", E_USER_ERROR);
$sql="SELECT fracture, COUNT(*) AS `count`
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'
GROUP BY fracture";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while ($row = mysql_fetch_array($result)) {
$fractures[$row['fracture']] = $row['count'];
$total += $row['count'];
}
PHP Code:<table>
<tr>
<td>
<?php echo round(($fractures['Anchor Bend'] / $total) * 100) . "%"; ?>
</td>
<td>
<?php echo round(($fractures['Break Back'] / $total) * 100) . "%"; ?>
</td>
<td>
<?php echo round(($fractures['Weld'] / $total) * 100) . "%"; ?>
</td>
</tr>
</table>
Also good to note that you don't need to connect to the database every time you issue a query, it doesn't close during execution unless you explicitly close it. Not a big deal, just saves some unnecessary typing; open once at the top and you're good.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 2, 2009, 13:32 #7
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks so much for your help!! That worked great! I have another query to find the MIN, MAX and AVG of the field 'beamPull' when the wire gauge is 3 and the endType is Loop. I also have one for when the wire gauge is 4 and the endType is Loop. Could I use the same principle for the one you helped me with and just group by "wireGauge"?
Thanks!
-
Dec 2, 2009, 13:41 #8
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oops! I forgot to show you the queries I mentioned in the post above.
Here they are:
PHP Code://query of min, max and avg for wire gauge 3 with endType Loop
$sql="SELECT MIN(beamPull), MAX(beamPull), AVG(beamPull)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$min3 = $row['MIN(beamPull)'];
$max3 = $row['MAX(beamPull)'];
$avg3 = $row['AVG(beamPull)'];
}
//query of min, max and avg for wire gauge 4 with endType Loop
$sql="SELECT MIN(beamPull), MAX(beamPull), AVG(beamPull)
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '4'
AND endType = 'Loop'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_assoc($result))
{
$min4 = $row['MIN(beamPull)'];
$max4 = $row['MAX(beamPull)'];
$avg4 = $row['AVG(beamPull)'];
}
-
Dec 2, 2009, 13:56 #9
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 2, 2009, 15:18 #10
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So I tried applying the first query to this second one for the min, max and avg and the result came back as '0' for the MIN value that I tested. I don't think I quite understand how to make this one work yet.
Here is what I came up with:
PHP Code://query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
$sql="SELECT beamPull, COUNT(*) AS 'minmaxavg'
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND endType = 'Loop'
GROUP BY wireGauge";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while($row = mysql_fetch_array($result)) {
$beamPull[$row['wireGauge']] = $row['minmaxavg'];
$total += $row['minmaxavg'];
}
HTML Code:<td> <?php echo $beamPull['MIN(minmaxavg)']; ?> </td>
Thanks.
-
Dec 2, 2009, 15:44 #11
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
You still need to select the same things you were selecting before (min/max/avg), you just add the beamPull column and group by it to get one row for each value in that column.
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 2, 2009, 16:57 #12
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I think I have the basic idea but am not quite understanding how this all works yet. I changed my query but still am not getting any results. Here is what I have now:
PHP Code://query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
$sql="SELECT beamPull, MIN(beamPull), MAX(beamPull), AVG(beamPull) AS 'minmaxavg'
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND endType = 'Loop'
GROUP BY wireGauge";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while($row = mysql_fetch_array($result)) {
$beamPull[$row['wireGauge']] = $row['minmaxavg'];
$total += $row['minmaxavg'];
}
I am also not completely understanding what is inside the while loop.
Can you explain so that I can hopefully understand and fix this?
Thanks!
-
Dec 2, 2009, 17:15 #13
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
You need to select the wireGauge column so you know what gauge the min/max/avg beam pull in each row correspond to. Selecting beamPull alone will give you nondeterministic results.
When you wrote "AS 'minmaxavg'", you're only creating an alias for that one column it follows, the average. You would also have had to use `backticks`, not 'single quotes', to create that alias. Right now your query is probably failing.
If you remove that alias, in your PHP code, there are 3 keys in each $row array: MIN(beamPull), MAX(beamPull) and AVG(beamPull).
You probably want to do something with those values. If you want to do the same thing you did with the totals before, then create 3 arrays, one for the minimums keyed by gauge, one for the maximums keyed by gauge, and one for the averages keyed by gauge. Or, you can create one big multidimensional array.
~
Some general advice... if you're coding something that you don't completely understand, do it one piece at a time. Don't try to write a bunch of code at once and see if it works, because you won't know which parts do and which parts don't.
Start with the SQL query here. Run it against your database, outside of PHP. That'll make sure you've written a valid query and you'll know what the results look like. THEN you'll have a better idea of how to write code to do something with those results.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 3, 2009, 12:24 #14
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks for the direction and advice. It took some time but I got it working and down to one query. If you are curious what I ended up with here is my code:
PHP Code:
//query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
$sql="SELECT wireGauge, MIN(beamPull) AS `min`, MAX(beamPull)AS `max`, AVG(beamPull) AS `avg`
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND endType = 'Loop'
GROUP BY wireGauge";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
while($row = mysql_fetch_array($result)) {
$beamPullmin[$row['wireGauge']] = $row['min'];
$beamPullmax[$row['wireGauge']] = $row['max'];
$beamPullavg[$row['wireGauge']] = $row['avg'];
}
HTML Code:<td> <?php echo $beamPullmin['3.0']; ?> </td> <td> <?php echo $beamPullmax['3.0']; ?> </td> <td> <?php echo round($beamPullavg['3.0']); ?> </td>
-
Dec 3, 2009, 12:49 #15
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Quick Question. Back to the 1st query you helped me with.
PHP Code://query of % that are anchor bend, break back or weld and wire gauge 3 with endType Loop
$sql="SELECT fracture, COUNT(*) AS `count`
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'
GROUP BY fracture";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while($row = mysql_fetch_array($result)) {
$fractures[$row['fracture']] = $row['count'];
$total += $row['count'];
}
Thanks
-
Dec 3, 2009, 13:01 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Dec 3, 2009, 13:12 #17
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok, so that makes sense, but the part I am still confused on is how to write my while loop and how to output the values for each gauge/fracture point.
Currently my while loop looks like:
PHP Code:$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while($row = mysql_fetch_array($result)) {
$fractures[$row['fracture']] = $row['count'];
$total += $row['count'];
}
HTML Code:<td> <?php echo round(($fractures['Anchor Bend']/$total) * 100) . "%"; ?> </td> <td> <?php echo round(($fractures['Break Back']/$total) * 100) . "%"; ?> </td> <td> <?php echo round(($fractures['Weld']/$total) * 100) . "%"; ?> </td>
Thanks!
-
Dec 7, 2009, 10:45 #18
- Join Date
- Dec 2009
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Could someone help me understand what is going on in the while loop? I have tested my query and it is pulling in data correctly. I am just not understanding how to take the results and get the data out correctly. I am wanting to figure out the percentage of Anchor Bend Fractures that are wireGauge 3. So basically I am wanting to know what percentage of Wire Gauge 3 fractures are Anchor Bend.
In my original query I did this:
PHP Code:$sql="SELECT fracture, COUNT(*) AS `count`
FROM welded_wire_ties
WHERE time between '$time1' and '$time2'
AND wireGauge = '3'
AND endType = 'Loop'
GROUP BY fracture";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$total = 0;
while($row = mysql_fetch_array($result)) {
$fractures[$row['fracture']] = $row['count'];
$total += $row['count'];
}
PHP Code:<?php echo round(($fractures['Anchor Bend']/$total) * 100) . "%"; ?>
PHP Code:$sql="SELECT wireGauge, fracture, COUNT(*) AS `count`
FROM welded_wire_ties
WHERE time BETWEEN '$time1' and '$time2'
AND endType = 'Loop'
GROUP BY wireGauge, fracture";
Bookmarks