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