pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Richards <michael@fastmail.ca>
To: alex@pilosoft.com
Cc: pgsql-sql@postgresql.org
Subject: Re: finding a maximum or minimum sum
Date: Mon, 11 Jun 2001 14:22:01 -0400 (EDT)
Message-ID: <3B250C49.0000FD.67770@frodo.searchcanada.ca> (raw)

> On Mon, 11 Jun 2001, Michael Richards wrote:
>
>> I run a select sum(amount) from payments group by userid
>> userid | sum
>> --------------
>> 1    | $500
>> 2    | $310
>> 3    |  $20
>>
>
>> I need to modify this query so it returns the minimum, maximum
>> and average sums. Is there any way I can do this?
> select sum(amount),min(amount),max(amount) from payments group by
> userid
>
> Is that what you mean? Or you want min/max _not_ to be grouped by
> userid?

I wanted
Max $500
Min $20
Avg $276

The trouble of course is that these values are already operated upon 
by sum in the group.

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Mon Jun 11 14:43:27 2001
Received: from kahuna.versions.com (kahuna.versions.com [204.17.38.231])
	by postgresql.org (8.11.3/8.11.1) with SMTP id f5BHsPE14003
	for <pgsql-sql@postgresql.org>; Mon, 11 Jun 2001 13:54:25 -0400 (EDT)
	(envelope-from pierre@kahuna.versions.com)
Received: (qmail 20148 invoked by uid 502); 11 Jun 2001 17:54:23 -0000
Message-ID: <20010611175423.20147.qmail@kahuna.versions.com>
From: pierre@kahuna.versions.com
Date: 11 Jun 2001 10:54:23 -0700
Subject: Re: Table design issue....
To: josh@agliodbs.com (Josh Berkus)
Date: Mon, 11 Jun 101 10:54:23 -0700 (MST)
Cc: pierre@kahuna.versions.com, pgsql-sql@postgresql.org
In-Reply-To: <web-70284@davinci.ethosmedia.com> from "Josh Berkus" at Jun 11, 1 10:44:01 am
Content-Type: text
X-Archive-Number: 200106/166
X-Sequence-Number: 3389

Wow, that is blunt. :-)

In all honesty, I can't imagine ever having to join all of that tables at once.
I had considered creating an attribute table previously, but was concerned
about performance when working to retrieve large numbers of records.

Perhaps, the best idea is the one I tried to not use, and that is a seperate
table that contains a copy of all of the attribute's that have the 
specified type. sort of a lookaside table. I was just concenred with
referential integrity.

Pierre

> 
> Pierre,
> 
> > Ideas? Comments? Suggestions? Am I being crazy?
> 
> Yes.  To be blunt, you've picked one of the worst possible database
> designs for any useful purpose.  This is, however, a common mistake as
> far too many books and training courses teach how to write SQL without
> teaching how to design a database.
> 
> What you really want is something like this:
> 
> CREATE TABLE tables (
> 	tableid CHAR(1) NOT NULL PRIMARY KEY
> 	);
> 
> CREATE TABLE attributes (
> 	tableid CHAR(1) NOT NULL REFERENCES tables(tableid),
> 	attributeid CHAR(1) NOT NULL,
> 	CONSTRAINT tab_attr_PK PRIMARY KEY (tableid, attributeid)	
> 	);
> 
> This makes your select statement possible:
> 	SELECT tableid FROM attributes WHERE attributeid = 'C'
> 	GROUP BY tableid ORDER BY tableid;
> 
> If your application requirements are more complicated than this, you
> need to either: a) hire a relational design expert, or b) become one.
> Books I'd recommend for the latter are Database Design for Mere Mortals
> and Practical Issues in Database Design (F. Pascal).
> 
> -Josh Berkus
> 
> 
> 
> 
> 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
> 




view thread (5+ messages)  latest in thread

Message-ID: <3B250C49.0000FD.67770@frodo.searchcanada.ca>
Permalink:  ../3B250C49.0000FD.67770@frodo.searchcanada.ca/
Also on:    postgresql.org/message-id/3B250C49.0000FD.67770@frodo.searchcanada.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: michael@fastmail.ca, alex@pilosoft.com
  Subject: Re: finding a maximum or minimum sum
  In-Reply-To: <3B250C49.0000FD.67770@frodo.searchcanada.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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