Skip site navigation (1) Skip section navigation (2)

Re: grouping query results

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Joolz <joolz(at)arbodienst-limburg(dot)nl>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: grouping query results
Date: 2005-02-28 18:10:16
Message-ID: 1109614216.26219.52.camel@lamb.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-php
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/
-------------------------------------------------------------------------

In response to

pgsql-php by date

Next:From: Mitch PirtleDate: 2005-02-28 18:14:38
Subject: Re: grouping query results
Previous:From: JoolzDate: 2005-02-28 09:48:23
Subject: grouping query results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group