pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedgrouping 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