SQL Group View to Get Grand Total and Details

[FooKelvin]

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

[Barand]

The second query would be

SELECT EmpID
, EmpName
, FormName
, Scoring 
FROM SubmittedForm 
WHERE EmpID = '0001' 
    AND submissionStatus <> 'Draft' 
    AND fiscalYear='2015'

 

[FooKelvin]

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

[Barand]

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.

[FooKelvin]

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.

[Barand]

 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

[FooKelvin]

Hi Barand, Thank You.

 

But with that query, how I able to select Top 20 Percent, and Grand Total ORDER BY DESC?

[Barand]

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?

[FooKelvin]

Oops Sorry, Barand, I didn't update the query. 

[Barand]

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

[benanamen]

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

[Barand]

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]

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

[benanamen]

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

[FooKelvin]

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.

[FooKelvin]

 

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.

[Barand]

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

[FooKelvin]

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