Percentage in mysql query

[mccawphoto]

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:

$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'];
} 

To print the percentages later on the page I have the following inside a table:

    <table>
        <tr>
            <td>
                 <?php echo "$bbcount1"; ?>
            </td>
            <td>
                 <?php echo "$bbcount0"; ?>
            </td>
            <td>
                 <?php echo "$bbcount2"; ?>
            </td>
        </tr>
    </table>

Thanks for any help to understand and solve this.

[Dan Grossman]

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.

[mccawphoto]

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:

$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)'];
    } 

Here is my query for the total rows:

$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)'];
    } 

Here is how I am printing it out and getting the percentage:

    <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>

Thanks for your help! I am starting to get better at this.

[Dan Grossman]

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

while($row = mysql_fetch_assoc($result)) 

could just be

$row = mysql_fetch_assoc($result); 

Or get rid of all 4 lines with

$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.

[mccawphoto]

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:

//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)'];
    } 

I am trying to get the percentage of the following:
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.

[Dan Grossman]

You can get all that information in one query:

$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'];
} 

Now to display the percentages:

    <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>

Let me know if any of that doesn't work, I didn't test it

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.

[mccawphoto]

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!

[mccawphoto]

Oops! I forgot to show you the queries I mentioned in the post above.

Here they are:

//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)'];
    } 

[Dan Grossman]

Could I use the same principle for the one you helped me with and just group by "wireGauge"?

Yep

[mccawphoto]

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:

//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'];
} 

Here is how I am outputting the value for MIN:

<td>
    <?php echo $beamPull['MIN(minmaxavg)']; ?>
</td>

What am I missing?
Thanks.

[Dan Grossman]

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.

[mccawphoto]

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:

//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 not sure if I need the "AS 'minmaxavg'" When you use "AS 'minmaxavg'" is that creating a "holder" to hold the values from the SELECT?
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!

[Dan Grossman]

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.

[mccawphoto]

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:

//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'];
} 

To output the values I did this:

<td>
    <?php echo $beamPullmin['3.0']; ?>
</td>
<td>
    <?php echo $beamPullmax['3.0']; ?>
</td>
<td>
    <?php echo round($beamPullavg['3.0']); ?>
</td>

Thanks again for all of your help!

[mccawphoto]

Quick Question. Back to the 1st query you helped me with.

//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'];
} 

Can I make it so that it works for all 4 wire gauges? So basically right now it finds the percentage for anchor bend, break back and weld fractures for wire guage 3. I also want to do this for wire gauge 2, 4 and 4.5. I know I can create a query for each one separately but I am thinking there should be a way to create one query to do it for all wire gauges. Can you group by more than one field? Would that solve it?

Thanks

[r937]

Can you group by more than one field?

as a matter of fact, yes you can

SELECT wireGauge
     , fracture
     , COUNT(*) AS `count` 
  FROM welded_wire_ties 
 WHERE time BETWEEN '$time1' AND '$time2' 
   AND endType = 'Loop' 
GROUP 
    BY wireGauge
     , fracture

[mccawphoto]

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:

$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'];
} 

And my output of values looks like:

<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>

How do I tell it the wire gauge along with the fracture point?
Thanks!

[mccawphoto]

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:

$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'];
} 

To output the percentage I did this:

<?php echo round(($fractures['Anchor Bend']/$total) * 100) . "%"; ?>

Since I have 4 different wire Gauges I want to write the query in such a way as to only have one query instead of one for each wire Gauge. So I wrote this query:

$sql="SELECT wireGauge, fracture, COUNT(*) AS `count`
FROM welded_wire_ties 
WHERE time BETWEEN '$time1' and '$time2' 
AND endType = 'Loop'
GROUP BY wireGauge, fracture"; 

I tested this query and ran it against the database and it works. The problem I am having is understanding how to pull the information out and show the percentages of each type of fracture that is a certain wire gauge.