...

View Full Version : How to echo row using 'WITH ROLLUP'



1andyw
05-05-2009, 10:22 AM
Hi,

How do I address the field created by the 'with rollup' condition?


$sql="SELECT hours,area,volunteer FROM hoursworked WITH ROLLUP";
$rs=mysql_query($sql);
while($row=mysql_fetch_array($rs){
echo '
"<p>Number of Hours Worked: " . $row['hours'] . "</p><p>Area: " . $row['area'] . "</p><p>Name of Volunteer: " . $row['volunteer'] . "</p>"';
}

Thanks,

Andy

venegal
05-05-2009, 10:49 AM
What exactly are you trying to do?
As far as I know, "WITH ROLLUP" is a modifier of "GROUP BY", which you do not use.
Furthermore, it should produce additional rows, not additional fields.

1andyw
05-05-2009, 02:14 PM
Yes, it produces an extra row that has as it's key, the sum of the row it affects, but no value. Just the key. My puzzle is how to I address that new row to make us of the sum it has produced?

venegal
05-05-2009, 02:58 PM
Well, what more is there to say that I didn't already tell you? "WITH ROLLUP" is a modifier of "GROUP BY", using the former without the latter should produce an error.

And if you want a sum, you first have to get a sum.

For example,


$sql="SELECT volunteer, SUM(hours) FROM hoursworked GROUP BY volunteer WITH ROLLUP";

will give you the sum of all worked hours for each volunteer, and additionally, as the last line of the result set, the sum of all worked hours (by all volunteers).

1andyw
05-05-2009, 04:16 PM
Yes, I understand what you have said. I had thought that using the built in ability to obtain a column sum would save the the step of running a second query. The first to iterate each persons record, the second to obtain the sum.

I almost had a shortcut.

However, I will continue to use the two query method.

Thanks for your help.

Andy



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum