FooKelvin Posted October 15, 2015 Share Posted October 15, 2015 (edited) Hi Guys, I have a system to keep track the scoring from different form. So, Initially when come to the reporting, i just accumulative different scoring from different form to get Grand Total. The Output that i have: The query that i use is : SELECT EmpID,EmpName,sum(Scoring) AS GrandTotal FROM [SubmittedForm] WHERE submissionStatus <> 'Draft' AND fiscalYear='2015' group by EmpID,EmpName ORDER BY GrandTotal DESC But, the feedback that i received, user request to have details, where this Grand Total come from which form? So I am thinking to allow user mouse over the Grand Total to show in details. Of course Only show individual details. For example, if i mouse over grand total for James, only James details will show up. Example: Query: SELECT EmpID,EmpName,[FormName],sum(Scoring) AS GrandTotal FROM [SubmittedForm] WHERE submissionStatus <> 'Draft' AND fiscalYear='2015' group by EmpID,EmpName,[FormName] ORDER BY GrandTotal DESC So, I am quite confusing is there any query can get what i need. I can know the accumulative of all forms and also individual forms. Or i only can do some if else matching on my programming sides? Edited October 15, 2015 by FooKelvin Quote Link to comment Share on other sites More sharing options...
Barand Posted October 15, 2015 Share Posted October 15, 2015 The second query would be SELECT EmpID , EmpName , FormName , Scoring FROM SubmittedForm WHERE EmpID = '0001' AND submissionStatus <> 'Draft' AND fiscalYear='2015' Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 21, 2015 Author Share Posted October 21, 2015 (edited) Hi Barand, i put the second query inside the for each? $sql = "SELECT EmpID,EmpName,sum(Scoring) AS GrandTotal FROM [evaluationSubmittedForm] WHERE submissionStatus <> 'Draft' AND fiscalYear=$currentYear group by EmpID,EmpName ORDER BY GrandTotal DESC"; $stmt = sqlsrv_query($conn, $sql); $data = array(); while (list($eid, $en,$gt) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$eid])) { $data[$eid]['EmpID'] = $eid; $data[$eid]['EmpName'] = $en; $data[$eid]['GrandTotal'] = $gt; } } /********************************************************** * create table from array data ***********************************************************/ foreach ($data as $eid=>$gdata) { $trows .= "<tr><td>{$gdata['EmpID']}</td>" . "<td class='cn'>{$gdata['EmpName']}</td>" . "<td>{$gdata['GrandTotal']}</td>"; $trows .= "</tr>\n"; foreach () { //SECOND QUERY GOES HERE? } } ?> <table border='1' style='border-collapse: collapse;'> <?php echo $tableHeads ?> <?php echo $trows; ?> </table> Expected Output: Current Output: Thank You Edited October 21, 2015 by FooKelvin Quote Link to comment Share on other sites More sharing options...
Barand Posted October 21, 2015 Share Posted October 21, 2015 Don't run queries inside loops, it's a performance killer. And in this case, why would you want to? For example, if i mouse over grand total for James, only James details will show up. You would use an ajax request to get the details you want. Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 21, 2015 Author Share Posted October 21, 2015 Sorry Barand, i can't get what you mean. you mean i can use ajax to get what output that i expect? After i take into consideration, i will prefer all data into one table instead of mouse hover. But i have no idea how to combine data from different query. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 21, 2015 Share Posted October 21, 2015 (edited) After i take into consideration, i will prefer all data into one table instead of mouse hover.. Then you only need one query $db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE); $sql = "SELECT empid , empname , formname , scoring FROM submittedform ORDER BY empid, formname"; $data = []; $res = $db->query($sql); // // process query, store in array by employee // while (list($eid, $ename, $fname, $score) = $res->fetch_row()) { if (!isset($data[$eid])) { // initialize array for employee $data[$eid] = ['emp' => $ename, 'total' => 0, 'items' => [] ]; } // accumulate item data $data[$eid]['items'][] = ['form' => $fname, 'score' => $score]; $data[$eid]['total'] += $score; } // // Now output the array // echo <<<TABLE <table border="1"> <tr> <th>Employee ID</th> <th>Employee Name</th> <th>Grand Total</th> <th>Forms</th> <th>Form Scores</th> </tr> TABLE; foreach ($data as $eid => $edata) { // how many items $numItems = count($edata['items']); echo "<tr> <td rowspan='$numItems'>$eid</td> <td rowspan='$numItems'>{$edata['emp']}</td> <td rowspan='$numItems'>{$edata['total']}</td>"; foreach ($edata['items'] as $k => $idata) { if ($k > 0) { echo "<tr>"; } echo "<td>{$idata['form']}</td><td>{$idata['score']}</td></tr>"; } } echo "</table>"; Edited October 21, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 22, 2015 Author Share Posted October 22, 2015 Hi Barand, Thank You. But with that query, how I able to select Top 20 Percent, and Grand Total ORDER BY DESC? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 Sorting the $data array prior to output will give the descending totals usort($data, function($a,$b){return $b['total']-$a['total'];}); how I able to select Top 20 Percent Where did that suddenly spring from? Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 22, 2015 Author Share Posted October 22, 2015 Oops Sorry, Barand, I didn't update the query. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 NOTE: usort() above should be uasort() to preserve the empid values. This will give the top 20 % uasort($data, function($a,$b){return $b['total']-$a['total'];}); $pcent20 = ceil(count($data)/5); $data = array_slice($data, 0, $pcent20, true); Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 22, 2015 Share Posted October 22, 2015 (edited) I think the op is asking how to do it in SQL. But with that query, how I able to select Top 20 Percent, and Grand Total ORDER BY DESC? Here is an example of using Mysql to calculate a percentage. Better to have the database do the work when it can. mysql> SELECT name, -> SUM(miles) AS 'miles/driver', -> (SUM(miles)*100)/@total AS 'percent of total miles' -> FROM driver_log GROUP BY name; +-------+--------------+------------------------+ | name | miles/driver | percent of total miles | +-------+--------------+------------------------+ | Ben | 362 | 16.712834718375 | | Henry | 911 | 42.059095106187 | | Suzi | 893 | 41.228070175439 | +-------+--------------+------------------------+ Edited October 22, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 The top 20% are not those that got 20% (or more) of the total. In your example, the top 66% got 40% (or more) Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2015 Share Posted October 22, 2015 (edited) Test data +------------------+-------+---------+----------+---------+ | submittedform_id | empid | empname | formname | scoring | +------------------+-------+---------+----------+---------+ | 1 | 1 | James | Form A | 4 | | 2 | 1 | James | Form B | 9 | | 3 | 1 | James | Form C | 8 | | 4 | 2 | Foo | Form A | 12 | | 5 | 2 | Foo | Form D | 10 | | 6 | 3 | bar | Form C | 30 | | 7 | 3 | bar | Form A | 5 | | 8 | 4 | Peter | Form B | 10 | | 9 | 4 | Peter | Form C | 4 | | 10 | 5 | Paul | Form A | 5 | | 11 | 5 | Paul | Form D | 12 | | 12 | 5 | Paul | Form B | 8 | | 13 | 6 | Mary | Form A | 9 | | 14 | 6 | Mary | Form B | 11 | | 15 | 7 | Jenny | Form B | 15 | | 16 | 7 | Jenny | Form C | 8 | | 17 | 8 | Steve | Form B | 9 | | 18 | 8 | Steve | Form D | 15 | | 19 | 9 | Tom | Form A | 12 | | 20 | 9 | Tom | Form C | 8 | | 21 | 10 | Zak | Form C | 9 | | 22 | 10 | Zak | Form D | 8 | +------------------+-------+---------+----------+---------+ 10 employees therefore top 20% are the two with highest scores SELECT sf.empid , sf.empname , sf.formname , sf.scoring , total , rank , pos , numemps FROM submittedform sf INNER JOIN ( SELECT empid , total , @pos := @pos + 1 as pos , @rank := IF(total=@prevtot, @rank, @pos) as rank , @prevtot := total FROM ( SELECT empid , SUM(scoring) as total FROM submittedform INNER JOIN (SELECT @pos:=0, @rank:=1,@prevtot:=0) as init GROUP BY empid ORDER BY SUM(scoring) DESC ) tot ) posn ON sf.empid = posn.empid CROSS JOIN ( SELECT COUNT(DISTINCT empid) as numemps FROM submittedform ) as ct WHERE CEIL(rank*100/numemps) <= 20 ORDER BY total DESC; +-------+---------+----------+---------+-------+------+------+---------+ | empid | empname | formname | scoring | total | rank | pos | numemps | +-------+---------+----------+---------+-------+------+------+---------+ | 3 | bar | Form C | 30 | 35 | 1 | 1 | 10 | | 3 | bar | Form A | 5 | 35 | 1 | 1 | 10 | | 5 | Paul | Form A | 5 | 25 | 2 | 2 | 10 | | 5 | Paul | Form D | 12 | 25 | 2 | 2 | 10 | | 5 | Paul | Form B | 8 | 25 | 2 | 2 | 10 | +-------+---------+----------+---------+-------+------+------+---------+ Edited October 22, 2015 by Barand typo correction Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 22, 2015 Share Posted October 22, 2015 (edited) The top 20% are not those that got 20% (or more) of the total. In your example, the top 66% got 40% (or more) @Barand, The example I posted is not an answer to the op's specific needs. It is STRICTLY a basic example to show how to calculate a percentage in MySQL. It of course needs to be modified/written for the specific results the OP wants. My position is that when the database can do something, that is where it should be done, not in code. Let the database do the work and get the data the way you want it from the start. The whole purpose of the DB is to manage data. Edited October 22, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 22, 2015 Author Share Posted October 22, 2015 Yup. The top 20 percent that i referring is : For example: 150 Employees out of 20% of the Highest Total. Meaning that, the 30 employees with the highest total will be selected. Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 23, 2015 Author Share Posted October 23, 2015 NOTE: usort() above should be uasort() to preserve the empid values. This will give the top 20 % uasort($data, function($a,$b){return $b['total']-$a['total'];}); $pcent20 = ceil(count($data)/5); $data = array_slice($data, 0, $pcent20, true); Am i putting the correct place: while (list($eid, $ename, $fname, $score) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$eid])) { // initialize array for employee $data[$eid] = array('emp' => $ename, 'total' => 0, 'items' => array()); } // accumulate item data $data[$eid]['items'][] = array('form' => $fname,'score' => $score); $data[$eid]['total'] += $score; } uasort($data, function($a,$b){return $b['total']-$a['total'];}); The output shows the total is sorting, but the forms displaying repeatedly. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2015 Share Posted October 23, 2015 Position looks fine - after accumulating the data into the array but before outputting the data from the array. If you are getting repeated items it could be the data. Can you post the output from echo '<pre>',print_r($data, true),'</pre>'; Quote Link to comment Share on other sites More sharing options...
FooKelvin Posted October 24, 2015 Author Share Posted October 24, 2015 Hi Barand, The sorting function is working correctly. =) btw, is there any different for $pcent20 = ceil(count($data)/5); $data = array_slice($data, 0, $pcent20, true); and also the top 20 for ms sql. SELECT Top 20 Percent EmpID,EmpName,sum(Scoring) AS GrandTotal FROM [SubmittedForm] WHERE submissionStatus <> 'Draft' AND fiscalYear='2015' group by EmpID,EmpName ORDER BY GrandTotal DESC Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.