X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4D4E2565A6 for ; Mon, 28 Feb 2005 18:12:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91801-10 for ; Mon, 28 Feb 2005 18:12:48 +0000 (GMT) Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.49.159.12]) by svr1.postgresql.org (Postfix) with ESMTP id 7131C52A4D for ; Mon, 28 Feb 2005 18:10:36 +0000 (GMT) Received: from leibniz.catalyst.net.nz ([202.49.159.7] helo=lamb.mcmillan.net.nz) by mail1.catalyst.net.nz with asmtp (TLS-1.0:DHE_RSA_3DES_EDE_CBC_SHA:24) (Exim 4.34) id 1D5pLV-0004Dc-3J; Tue, 01 Mar 2005 07:10:17 +1300 Received: from lamb.mcmillan.net.nz (lamb.mcmillan.net.nz [127.0.0.1]) by lamb.mcmillan.net.nz (Postfix) with ESMTP id 7174DAD98583; Tue, 1 Mar 2005 07:10:16 +1300 (NZDT) Subject: Re: grouping query results From: Andrew McMillan To: Joolz Cc: pgsql-php@postgresql.org In-Reply-To: <36567.10.0.4.254.1109584103.squirrel@webmail.arbodienst-limburg.nl> References: <36567.10.0.4.254.1109584103.squirrel@webmail.arbodienst-limburg.nl> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-GPs3EFmiBQzFpC2wq7i+" Date: Tue, 01 Mar 2005 07:10:16 +1300 Message-Id: <1109614216.26219.52.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.0.3 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/39 X-Sequence-Number: 3013 --=-GPs3EFmiBQzFpC2wq7i+ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Mon, 2005-02-28 at 10:48 +0100, Joolz wrote: > Hello everyone, >=20 > In a PHP / Postgresql application I have to make a lot of lists like > this: >=20 > THE DATA STRUCTURE > employee_number, employee_name, department >=20 > THE OUTPUT > ------------------------------------ > employee_number employee_name >=20 > department X > 1 Johnson > 22 Jackson > subtotal: 2 employees >=20 > department Y > 222 Smith > 3 Doe > 44 Jameson > subtotal: 3 employees >=20 > grand total: 5 employees > ------------------------------------ >=20 > I see 2 ways to solve this: >=20 > - use SELECT for the detail lines and SELECT COUNT (*) for the (sub) > totals >=20 > - SELECT the whole lot and let PHP do the grouping and counting >=20 > The second solution seems old fashioned to me and the first has a > catch: all queries have to be made within a single transaction. >=20 > 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 =3D 0; $dtotal =3D 0; $last_department =3D "no department"; for( $i=3D0 $i < rows; $row =3D pg_Fetch_Object(..., $i) { if ( $row->department !=3D $last_department ) { if ( $i > 0 ) { print_total( "subtotal", $dtotal ); } $dtotal =3D 0; $last_department =3D $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/ ------------------------------------------------------------------------- --=-GPs3EFmiBQzFpC2wq7i+ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQBCI16IjJA0f48GgBIRAqw2AJ9TTCTAKhXHj4wqDgL6EKFIbqBucACcDmUR KtAqmbWt616BPdeJTAAnCFw= =YXeT -----END PGP SIGNATURE----- --=-GPs3EFmiBQzFpC2wq7i+--