Re: grouping query results

On Mon, 2005-02-28 at 10:48 +0100, Joolz wrote:
> Hello everyone,
>
> In a PHP / Postgresql application I have to make a lot of lists like
> this:
>
> THE DATA STRUCTURE
> employee_number, employee_name, department
>
> THE OUTPUT
> ------------------------------------
> employee_number employee_name
>
> department X
> 1 Johnson
> 22 Jackson
> subtotal: 2 employees
>
> department Y
> 222 Smith
> 3 Doe
> 44 Jameson
> subtotal: 3 employees
>
> grand total: 5 employees
> ------------------------------------
>
> I see 2 ways to solve this:
>
> - use SELECT for the detail lines and SELECT COUNT (*) for the (sub)
> totals
>
> - SELECT the whole lot and let PHP do the grouping and counting
>
> The second solution seems old fashioned to me and the first has a
> catch: all queries have to be made within a single transaction.
>
> I have the feeling that this is a very common question, how do you
> people handle it? I looked at a lot of script sites, but no luck.

I would tend to use the second solution purely for performance reasons
since the first solution will require a select plus one select per
department, which won't scale well to lots of departments.

function print_total( $label, $total ) {
echo "%s: %d employees";
}

SELECT employee_number, employee_name, department FROM xxx ORDER BY
department

$gtotal = 0;
$dtotal = 0;
$last_department = "no department";
for( $i=0 $i < rows; $row = pg_Fetch_Object(..., $i) {
if ( $row->department != $last_department ) {
if ( $i > 0 ) {
print_total( "subtotal", $dtotal );
}
$dtotal = 0;
$last_department = $row->department;
}
printf( "%5d %s", $row->employee_number, $row->employee_name );
$dtotal++;
$gtotal++;
}

print_total( "subtotal", $dtotal );
print_total( "grand total", $gtotal );

Cheers,
Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
What are they doing now? http://schoolreunions.co.nz/
-------------------------------------------------------------------------