List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:November 10 2005 8:10pm
Subject:Re: GROUP BY / HAVING / Aggregrates
View as plain text  
On 11/10/05, Peter Brawley <peter.brawley@stripped> wrote:
> Scott,
>
>  >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..
>
> Your schema is k -> de -> de_kpi, so a query that groups by k.id or
> k.aid will not show individual row values from the 'de' table. Is the
> following what you mean?
>
> SELECT
>   k.id,
>   k.aid,
>   ...,
>   AVG(CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)))
>   AS 'Avg Effiency',
>   k.hours - SUM(de.dhours) AS 'Idle time',
> FROM
>   Kronos AS k
>   LEFT JOIN de_metrics AS de ON k.id=de.kid
>   LEFT JOIN de_kpi AS dk ON de.decid=dk.cat_id
>         AND dk.month=month('2005-11-04') AND dk.year=year('2005-11-04')
> WHERE
>   k.date='2005-11-04'
> GROUP BY k.aid
> ORDER BY
>   k.training, k.shift, k.id;
>
> PB
>
> -----
>
> Scott Hamm wrote:
>
> >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--
> >
> >
> >
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.8/166 - Release Date: 11/10/2005
>
>


Mine was so far:

DECLARE @sdate smalldatetime
SET @sdate='2005-11-04'
SELECT
	K.shift,
	A.LastName + ', ' + A.FirstName AS 'Name',
	K.Hours AS 'Krono',
	K.PTO AS 'PTO',
	DE.DECID AS 'CatID',
	DC.Category AS 'Category',
	DE.DQTY AS 'QTY',
	DE.DHours AS 'Hours',
	DK.goal AS 'Goal',
	CAST((de.dqty/de.dhours) AS DECIMAL(5,0)) AS 'PPH',
	CAST( (de.dqty/(dk.goal*de.dhours))*100 AS DECIMAL(5,0)) AS 'Effiency',
	P.AVGEFF AS 'Avg_Eff',
	K.hours-P.TOTHRS AS 'Time_rem'
FROM
	(
		SELECT
			DE.KID,
			sum(DE.DHours) AS TOTHRS,
			CAST(AVG((de.dqty/(dk.goal*de.dhours))*100) AS DECIMAL(5,0)) AS 'AVGEFF'	
		FROM
			DE_Metrics DE
		LEFT JOIN
			DE_KPI DK
				ON DK.cat_id=DE.DECID
				AND DK.month=month(@sdate)
				AND DK.year=year(@sdate)
		WHERE
			DE.KID IN
				(
				SELECT
					K.ID
				FROM
					Kronos K
				WHERE
					date=@sdate
				)
		GROUP BY
			DE.KID
	)AS P
LEFT JOIN
	Kronos K ON K.ID=P.KID
LEFT JOIN
	Associates A ON A.ID=K.AID
LEFT JOIN
	DE_Metrics DE ON DE.KID=K.ID
LEFT JOIN
	DECategories DC ON DC.ID=DE.DECID
LEFT JOIN
	DE_KPI DK
		ON DK.cat_id=DE.DECID
		AND DK.month=month(@sdate)
		AND DK.year=year(@sdate);

I will try yours and see if that what I was looking for.

Thanks.
Thread
GROUP BY / HAVING / AggregratesScott Hamm10 Nov
  • Re: GROUP BY / HAVING / AggregratesPeter Brawley10 Nov
    • Re: GROUP BY / HAVING / AggregratesScott Hamm10 Nov