How to get a correct Average in a pivot table

[Failing_Solutions]

Hi I'm looking for a little direction trying to calculate averages with a pivot table type query

 

I run a query like this:

SELECT IFNULL(Worker, 'Totals') AS Operator,
`Week 1`

FROM ( SELECT w.worker_name as 'Worker',
ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1'
FROM production
JOIN workers AS w USING (worker_id) WHERE product_id='1321' GROUP BY worker_id ) AS sums

And get this...

Operator Week1

ABaits 0.00

DHarris 0.00

JAvalas 665.14

KIgner 0.00

MAhe 0.00

AReynolds 196.43

JWhitt 0.00

RAloney 422.97

AStorms 148.40

IGlesias 716.62

 

Which is only correct if in this time period (Week 1) the user has only 1 record. For example JAvalas Week 1 should read 665.15/4=166.29 (there are 4 records this week for him)

But

AReynolds (196.43) is actually correct because in this period he only had 1 record

 

Since I'm using SUM everything is actually working correctly for what I'm asking, but I need to introduce a way to AVG() these groupd results.

 

I've tried introuducing COUNT(*) at ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60*(COUNT(*)),0)),2) As 'Week 1' to modify the 60 divisor, I've tried using ROUND(AVG(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' instead of SUM() but end up with some very odd results. And I've tried to sum(production_net)/sum(production_time) but get an invalid use of group functions.

 

I'm not sure what I can do to get the averages here.

 

Any help or advise is always welcome,

Thank you

Edited January 24, 2013 by Failing_Solutions

[Barand]

What is the average you want to calculate?

 

How are your tables structured?

SHOW CREATE TABLE production

SHOW CREATE TABLE workers

[Failing_Solutions]

Hi I'm trying to calculate the Parts Per Hour

production_net/(production_time/60) where production time is saved in total minutes ((300) = 5 hours))

 

So when grouping by a time period like a week or month I need to average the average. For example,

 

if I have 1 record then this formula is perfect.... production_net/(production_time/60)

but if I have more then 1 record for then I need to modify the formula accordingly like production_net/(production_time/(60*X))

 

CREATE TABLE IF NOT EXISTS `production` (
`production_id` int(4) NOT NULL AUTO_INCREMENT,
`product_id` int(4) NOT NULL,
`production_date` date NOT NULL,
`mix_date` date NOT NULL,
`production_batch` varchar(15) NOT NULL,
`ir_number` varchar(65) NOT NULL,
`production_shift` int(4) NOT NULL,
`worker_id` int(4) NOT NULL,
`production_total` int(4) NOT NULL,
`production_defects` int(4) NOT NULL,
`production_net` int(4) NOT NULL,
`cure_date` date NOT NULL,
`production_time` varchar(12) NOT NULL,
`location_id` int(4) NOT NULL,
`mix_ir_number` varchar(70) NOT NULL,
`compound_number` varchar(65) NOT NULL,
`received_id` int(4) NOT NULL,
`production_entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`production_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1399 ;

 

CREATE TABLE IF NOT EXISTS `workers` (
`worker_id` int(4) NOT NULL AUTO_INCREMENT,
`worker_name` varchar(60) NOT NULL,
`worker_description` varchar(80) NOT NULL,
`worker_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ;

Edited January 25, 2013 by Failing_Solutions

[Failing_Solutions]

Hi Barand

 

I also was looking at your baagrid link in your sig it seemed to have some pivot table functionality which I thought maybe handy for me to look at. Noticed it didn't have a sample database so found it hard to thoroughly look at.

 

Thanks,

Don

Edited January 25, 2013 by Failing_Solutions

[Failing_Solutions]

In the end I figured it out

 

I needed to replace

ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1'

 

 

With

ROUND(AVG(production_net/(production_time/60)),2) As 'Week 1',

 

By introducing the if statement with the averages I was getting incorrect results because I was counting records that should not have been counted to figure the average.

[Barand]

Hi Barand

 

I also was looking at your baagrid link in your sig it seemed to have some pivot table functionality which I thought maybe handy for me to look at. Noticed it didn't have a sample database so found it hard to thoroughly look at.

 

Thanks,

Don

 

It doesn't need any specific database Just use tutorial with your own data. The Xtab will do averages but based on SUM/COUNT whereas you need SUM(production_net)/SUM(production_time)

[Barand]

In the end I figured it out

 

I needed to replace

ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1'

 

 

With

ROUND(AVG(production_net/(production_time/60)),2) As 'Week 1',

 

By introducing the if statement with the averages I was getting incorrect results because I was counting records that should not have been counted to figure the average.

 

That new code will produce an average of the averages, which doesn't work. As I said before you need SUM(net)/SUM(time).

 

As an example

 

$a = array (
	    array(100, 25),
	    array(20,35),
	    array(20,15)
    );
$k = count($a);
$av = 0;
foreach ($a as $B) {
   $av += $b[0]/$b[1];
}
printf('Avg of averages is %10.6f<br>', $av/$k);  // --> 1.968254

$c=$d=0;
foreach ($a as $B) {
   $c += $b[0];
   $d += $b[1];
}
printf('True average is %10.6f<br>', $c/$d);  // --> 1.866667

[Barand]

Here's my version. You probably need to change the date range in the query

 

<?php
$mysqli = new mysqli('localhost','user','pwd','dbname');
?>
<html>
<head>
<meta name="generator" content="PhpED Version 8.1 (Build 8115)">
<title>Sample</title>
<style type="text/css">
th {
   background-color: #369;
   color: white;
   font-weight: 600;
}
td {
   background-color: #ccc;
   text-align: right;
   width: 100px;
}

.tot {
   background-color: #666;
   color: white;
}

</style>
</head>
<body>
<?php

$sql = "SELECT wk.worker_id, w.worker_name, wk.weekno,
   SUM(p.production_net) as prodnet,
   SUM(p.production_time/60) as prodtime
FROM (
   SELECT worker_id, weekno
   FROM
   (SELECT DISTINCT worker_id FROM production) a
   JOIN
   (SELECT DISTINCT CONCAT_WS('-',YEAR(production_date),WEEK(production_date)) as weekno
    FROM production
    WHERE production_date BETWEEN '2013-01-01' AND '2013-01-28') b
   ) wk
INNER JOIN workers w USING (worker_id)
LEFT JOIN production p
ON wk.weekno = CONCAT_WS('-',YEAR(p.production_date),WEEK(p.production_date))
   AND wk.worker_id = p.worker_id
   AND product_id='1321'
GROUP BY wk.worker_id, wk.weekno";

$res = $mysqli->query($sql) or die($mysqli->error);
$data = $totals = array();
while ($row = $res->fetch_assoc()) {
   $data[$row['worker_name']][$row['weekno']] = sprintf('%8.2f',$row['prodtime']?$row['prodnet']/$row['prodtime']: 0);
   $total[$row['weekno']]['prodnet'] += $row['prodnet'];
   $total[$row['weekno']]['prodtime'] += $row['prodtime'];
}

/**
* Output the pivot table
*/
echo "<table border='0' cellspacing='1' cellpadding='2'>\n";
list( ,$datesarray) = each($data);
$dates = array_keys($datesarray);
echo "<tr><th>Worker</th><th>" . join('</th><th>', $dates) . "</th></tr>\n";
foreach ($data as $worker => $wktots) {
   echo "<tr><td>$worker</td><td>" . join('</td><td>', $wktots) . "</td></tr>\n";
}
/**
* totals
*/
echo "<tr><th class='tot'>Totals</th>";
foreach ($total as $tot) {
   printf('<td class="tot">%8.2f</td>', $tot['prodnet']/$tot['prodtime']);
}
echo "</tr></table>\n";

?>
</body>
</html>

[Failing_Solutions]

Hi Barand as you accurately pointed out my solution wasn't working. The problem was with where I was calling the AVG() function with conjuction to the IF statment. I should have changed it, however I do appreciate your continued help even after I had marked it solved.

 

I WAS USING INCORRECTLY using:

ROUND(AVG(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1',

 

CORRECT:

IF(Week(production_date)=1, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week1,

 

I ended up with this type of query...

SELECT
IFNULL(Worker,'Totals') Worker,
Week1, Week2, Week3, Week4, Week5, Week6, Week7, Week8, Week9, Week10, Week11, `Week12`,
quantity AS Count,
ttl as 'Avg'
FROM (
SELECT
w.worker_name as Worker,
IF(Week(production_date)=1, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week1,
IF(Week(production_date)=2, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week2,
IF(Week(production_date)=3, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week3,
IF(Week(production_date)=4, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week4,
IF(Week(production_date)=5, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week5,
IF(Week(production_date)=6, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week6,
IF(Week(production_date)=7, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week7,
IF(Week(production_date)=8, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week8,
IF(Week(production_date)=9, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week9,
IF(Week(production_date)=10, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week10,
IF(Week(production_date)=11, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week11,
IF(Week(production_date)=12, ROUND(AVG(production_net/(production_time/60)),2),0) AS Week12,
COUNT(*) As quantity,
ROUND(AVG(production_net/(production_time/60)), 2) AS ttl
FROM production p
JOIN workers w USING(worker_id)
WHERE product_id='1321'
GROUP BY w.worker_name
WITH ROLLUP
) as poo

 

I'm using PHP to build the query based on user date inputs.

 

Many Many Thanks,

Don

[Barand]

You are still calculating an average of averages instead of SUM()/SUM()

 

ROUND(AVG(production_net/(production_time/60)), 2) AS ttl

[Failing_Solutions]

True,

 

Whats the difference in a "average of sums" and "average of averages"? mathmatically speaking

 

User A values...

value 1: 300 parts in 480 mins

value 2: 320 parts in 480 mins

value 3: 305 parts in 450 hours

 

Avg of Sums

SUM(parts) = 925

SUM(hours/60) = 15.42 hours

Parts Per Hour = 925/23.5 = 39.36

 

AVG of AVG

value 1 avg is 300/(480/60) = 37.5

value 2 avg is 320/(480/60) = 40.0

value 3 avg is 305/(450/60) = 40.67

Parts Per Hour = Average of (37.5+40.0+40.67)/3 = 39.39

 

giving up the decimal rounding I did the results are the same or am I missing something??

[Barand]

I pointed it out in an earlier post http://forums.phpfreaks.com/topic/273588-how-to-get-a-correct-average-in-a-pivot-table/?do=findComment&comment=1408278.

 

Averages of averages are mathematically incorrect.

[Failing_Solutions]

Thank you, I just re-read all your responses.

 

I also want to comment specifically on the time you spent creating a sample page for me. That was very very nice of you. I just put code into a page locally and can really appreciate the fact you used my table names and variables. (I had to convert mysqlite to mysql no biggy, I also realize that mysql support will be deprecated) .

 

Only thing I'm not liking is that the query is getting every user, where the ideally I would only get workers who have actually made that part. I'm fiiddling with it now.

 

Thank you,

Don

 

[Barand]

You probably just need to change

 

(SELECT DISTINCT worker_id FROM production) a

to

(SELECT DISTINCT worker_id FROM production WHERE product_id=1321) a

 

It isn't mysqlite, it's mysqli (mysql improved), the replacement for mysql.

[Failing_Solutions]

Yep that was it. Thanks for all the help, I'm still digesting your coding style (PHP)

 

I'm self taught and it looks you're using some very shorthand methods, which would be great if I understood them. But I have to disect it line by line and lookup as I go, either way, it is a very good learning experience and very much appreicated.

 

Thanks,

Don