List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:November 10 2005 5:47pm
Subject:GROUP BY / HAVING / Aggregrates
View as plain text  
I created a report that shows effiency for each associate (K.AID). I
am trying to figure out how to use GROUP BY to AVG(Effiency) for each
K.AID in subquery.  Originally I used temporary tables (4) to figure
out the average effiency for each K.AID. That often left hanging
temporary tables and took long time to process information. And I also
need to sum(dhours) on hours worked for each category and to subtract
that from K.hours (clocked in hours) which shows the idle time for
each K.AID. I already RTFM and STFW trying to figure out how to use
GROUP BY to no avail.  Here is the detailed information and example of
my current database that I am working on.  Any helpful insights or
suggestions would be of a big help and appreciated..

Thanks,


Scott

--CODE--

mysql> EXPLAIN KRONOS;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int(10)      | NO   | PRI | NULL    | auto_increment |
| AID      | int(10)      | NO   | MUL |         |                |
| DATE     | datetime     | NO   |     |         |                |
| HOURS    | decimal(4,2) | NO   |     |         |                |
| PTO      | decimal(4,2) | YES  |     | NULL    |                |
| Training | tinyint(3)   | NO   |     |         |                |
| LocID    | int(10)      | YES  |     | NULL    |                |
| Shift    | int(10)      | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

mysql> EXPLAIN DE_METRICS;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| ID     | int(10)      | NO   | PRI | NULL    | auto_increment |
| KID    | int(10)      | NO   | MUL |         |                |
| DECID  | int(10)      | NO   |     |         |                |
| DQTY   | int(10)      | NO   |     |         |                |
| DHours | decimal(4,2) | NO   |     |         |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> EXPLAIN DE_KPI;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cat_id       | int(10)     | NO   | PRI |         |       |
| month        | smallint(5) | NO   | PRI |         |       |
| year         | int(10)     | NO   | PRI |         |       |
| goal         | int(10)     | NO   |     |         |       |
| AdminID      | int(10)     | NO   |     |         |       |
| updated_date | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> SELECT
    ->  k.id,
    ->  k.training,
    ->  k.shift,
    ->  k.aid,
    ->  k.hours,
    ->  k.pto,
    ->  de.decid,
    ->  de.dqty,
    ->  de.dhours,
    ->  CAST((de.dqty/de.dhours) AS DECIMAL(5,0)) AS 'PPH',
    ->  dk.goal,
    ->  CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)) AS 'Effiency'
    -> FROM
    ->  Kronos k
    -> LEFT JOIN
    ->  de_metrics DE
    ->          ON DE.KID=K.ID
    -> LEFT JOIN
    ->  DE_KPI DK
    ->          ON DK.cat_id=DE.DECID
    ->          AND DK.month=month('2005-11-04')
    ->          AND DK.year=year('2005-11-04')
    -> WHERE
    ->  K.date='2005-11-04'
    -> ORDER BY
    ->  k.training,
    ->  k.shift,
    ->  k.id;
+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
| id | training | shift | aid  | hours | pto  | decid | dqty | dhours
| PPH    | goal | Effiency |
+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
|  2 |        0 |     1 |   73 |  8.08 | 0.00 |    20 |  959 |   6.76
| 141.86 |   50 |   283.73 |
|  4 |        0 |     1 | 5179 |  6.98 | 0.00 |     2 |  109 |   3.60
|  30.28 |   60 |    50.46 |
|  4 |        0 |     1 | 5179 |  6.98 | 0.00 |    20 |   21 |   0.20
| 105.00 |   50 |   210.00 |
|  5 |        0 |     1 |   26 |  7.95 | 0.00 |    19 |   52 |   1.13
|  46.02 |   46 |   100.04 |
|  5 |        0 |     1 |   26 |  7.95 | 0.00 |    20 |  441 |   5.28
|  83.52 |   50 |   167.05 |
|  5 |        0 |     1 |   26 |  7.95 | 0.00 |    22 |   17 |   0.48
|  35.42 |  110 |    32.20 |
|  6 |        0 |     1 |   30 |  9.73 | 0.00 |    10 |    0 |   9.23
|   0.00 | NULL |     NULL |
|  8 |        0 |     1 | 5080 |  7.25 | 0.00 |    10 |    0 |   5.41
|   0.00 | NULL |     NULL |
|  9 |        0 |     1 | 4589 |  8.08 | 0.00 |    20 |  644 |   6.85
|  94.01 |   50 |   188.03 |
| 10 |        0 |     1 | 4697 |  7.93 | 0.00 |     1 |  129 |   1.28
| 100.78 |   65 |   155.05 |
| 10 |        0 |     1 | 4697 |  7.93 | 0.00 |     2 |  160 |   3.39
|  47.20 |   60 |    78.66 |
| 11 |        0 |     1 | 4420 |  7.57 | 0.00 |     1 |   40 |   0.65
|  61.54 |   65 |    94.67 |
| 11 |        0 |     1 | 4420 |  7.57 | 0.00 |    10 |    0 |   5.46
|   0.00 | NULL |     NULL |
| 12 |        0 |     1 |  336 |  7.28 | 0.00 |    19 |   19 |   0.35
|  54.29 |   46 |   118.01 |
| 12 |        0 |     1 |  336 |  7.28 | 0.00 |    20 |  645 |   5.66
| 113.96 |   50 |   227.92 |
| 13 |        0 |     1 |   36 |  8.13 | 0.00 |    20 |  348 |   6.05
|  57.52 |   50 |   115.04 |
| 14 |        0 |     1 | 4497 |  7.58 | 0.00 |    19 |  104 |   2.15
|  48.37 |   46 |   105.16 |
| 14 |        0 |     1 | 4497 |  7.58 | 0.00 |    20 |  328 |   3.61
|  90.86 |   50 |   181.72 |
| 15 |        0 |     1 |  321 |  7.92 | 0.00 |    20 |  371 |   6.13
|  60.52 |   50 |   121.04 |
| 16 |        0 |     1 |  484 |  7.33 | 0.00 |    10 |    0 |   5.17
|   0.00 | NULL |     NULL |
| 16 |        0 |     1 |  484 |  7.33 | 0.00 |    20 |  131 |   1.41
|  92.91 |   50 |   185.82 |
| 17 |        0 |     1 | 5605 |  5.48 | 0.00 |     2 |   25 |   0.54
|  46.30 |   60 |    77.16 |
| 17 |        0 |     1 | 5605 |  5.48 | 0.00 |    10 |    0 |   0.33
|   0.00 | NULL |     NULL |
| 17 |        0 |     1 | 5605 |  5.48 | 0.00 |    20 |  334 |   3.15
| 106.03 |   50 |   212.06 |
| 18 |        0 |     1 |  119 |  7.40 | 0.00 |    20 |  688 |   6.03
| 114.10 |   50 |   228.19 |
| 20 |        0 |     1 | 4381 |  6.83 | 0.00 |    20 |  374 |   5.01
|  74.65 |   50 |   149.30 |
| 22 |        0 |     1 | 4382 |  7.32 | 0.00 |    10 |    0 |   6.72
|   0.00 | NULL |     NULL |
|  3 |        0 |     2 |  721 |  7.92 | 0.00 |     1 |  106 |   1.35
|  78.52 |   65 |   120.80 |
|  3 |        0 |     2 |  721 |  7.92 | 0.00 |     2 |  113 |   1.58
|  71.52 |   60 |   119.20 |
|  3 |        0 |     2 |  721 |  7.92 | 0.00 |    20 |   79 |   0.71
| 111.27 |   50 |   222.54 |
|  7 |        0 |     2 | 2457 |  8.00 | 0.00 |     1 |  150 |   1.34
| 111.94 |   65 |   172.22 |
|  7 |        0 |     2 | 2457 |  8.00 | 0.00 |     2 |  178 |   1.63
| 109.20 |   60 |   182.00 |
|  7 |        0 |     2 | 2457 |  8.00 | 0.00 |     4 |   28 |   0.30
|  93.33 |   75 |   124.44 |
|  7 |        0 |     2 | 2457 |  8.00 | 0.00 |    19 |   12 |   0.12
| 100.00 |   46 |   217.39 |
| 19 |        0 |     2 | 4474 |  8.00 | 0.00 |  NULL | NULL |   NULL
|   NULL | NULL |     NULL |
| 21 |        0 |     2 | 1016 |  8.02 | 0.00 |    10 |    0 |   7.52
|   0.00 | NULL |     NULL |
| 23 |        0 |     2 | 4561 |  8.65 | 0.00 |    10 |    0 |   8.15
|   0.00 | NULL |     NULL |
|  1 |        1 |     1 | 6105 |  7.53 | 0.00 |    20 |  754 |   6.25
| 120.64 |   50 |   241.28 |
+----+----------+-------+------+-------+------+-------+------+--------+--------+------+----------+
38 rows in set (0.00 sec)

--END OF CODE--
Thread
GROUP BY / HAVING / AggregratesScott Hamm10 Nov
  • Re: GROUP BY / HAVING / AggregratesPeter Brawley10 Nov
    • Re: GROUP BY / HAVING / AggregratesScott Hamm10 Nov