Jump to content

SQL Group View to Get Grand Total and Details


FooKelvin

Recommended Posts

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:

 

post-179514-0-82719800-1444894376_thumb.jpg

 

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:

post-179514-0-67213600-1444894691_thumb.jpg

 

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 by FooKelvin
Link to comment
Share on other sites

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:

post-179514-0-16838300-1445414842_thumb.jpg

 

Current Output:

post-179514-0-26993100-1445414914_thumb.jpg

 

Thank You

Edited by FooKelvin
Link to comment
Share on other sites

 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 by Barand
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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 by Barand
typo correction
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.