pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
grouping query results
4+ messages / 4 participants
[nested] [flat]

* grouping query results
@ 2005-02-28 09:48 Joolz <joolz@arbodienst-limburg.nl>
  2005-02-28 18:10 ` Re: grouping query results Andrew McMillan <andrew@catalyst.net.nz>
  2005-02-28 18:14 ` Re: grouping query results Mitch Pirtle <mitch.pirtle@gmail.com>
  2005-02-28 18:30 ` Re: grouping query results Bruno Wolff III <bruno@wolff.to>
  0 siblings, 3 replies; 4+ messages in thread

From: Joolz @ 2005-02-28 09:48 UTC (permalink / raw)
  To: pgsql-php

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.

Thanks for any ideas / feedback!




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: grouping query results
  2005-02-28 09:48 grouping query results Joolz <joolz@arbodienst-limburg.nl>
@ 2005-02-28 18:10 ` Andrew McMillan <andrew@catalyst.net.nz>
  2 siblings, 0 replies; 4+ messages in thread

From: Andrew McMillan @ 2005-02-28 18:10 UTC (permalink / raw)
  To: Joolz <joolz@arbodienst-limburg.nl>; +Cc: 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/
-------------------------------------------------------------------------

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: grouping query results
  2005-02-28 09:48 grouping query results Joolz <joolz@arbodienst-limburg.nl>
@ 2005-02-28 18:14 ` Mitch Pirtle <mitch.pirtle@gmail.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Mitch Pirtle @ 2005-02-28 18:14 UTC (permalink / raw)
  To: pgsql-php

On Mon, 28 Feb 2005 10:48:23 +0100 (CET), Joolz
<joolz@arbodienst-limburg.nl> wrote:
> 
> 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.

What I do is run a single query for all employees, then iterate over
the array in PHP for the counts.

The alternative of running several queries seems a bit heavy-handed to
me, but that is because I am always trying to make life easier for the
database server :-)

Note that many database libraries (PEAR::DB, ADOdb) also have
capabilities of returning counts of recordsets, so with two queries
you would be done. But I still like the first approach, as it is not
tied to the database or require external libraries to support.

-- Mitch



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: grouping query results
  2005-02-28 09:48 grouping query results Joolz <joolz@arbodienst-limburg.nl>
@ 2005-02-28 18:30 ` Bruno Wolff III <bruno@wolff.to>
  2 siblings, 0 replies; 4+ messages in thread

From: Bruno Wolff III @ 2005-02-28 18:30 UTC (permalink / raw)
  To: Joolz <joolz@arbodienst-limburg.nl>; +Cc: pgsql-php

On Mon, Feb 28, 2005 at 10:48:23 +0100,
  Joolz <joolz@arbodienst-limburg.nl> 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

You can use ORDER BY to do most of the grouping work. The app just
needs to check when the department changes and keep counters.

> 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 think more than that. I think you also want the transaction isolation
level set to serializeable if you want a consistant report.

> 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 suggest method 2 using an ORDER BY to produce the detail lines
in the correct order.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-02-28 18:30 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-28 09:48 grouping query results Joolz <joolz@arbodienst-limburg.nl>
2005-02-28 18:10 ` Andrew McMillan <andrew@catalyst.net.nz>
2005-02-28 18:14 ` Mitch Pirtle <mitch.pirtle@gmail.com>
2005-02-28 18:30 ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox