Feature Request, aggregate functions distinct on

From: luda posch <ludaludaluda(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Feature Request, aggregate functions distinct on
Date: 2011-04-26 22:24:28
Message-ID: BANLkTikgwqDj7kGAGVH-exX8K5z9Bsf_Tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have used and it is quite useful the 'distinct' keyword within aggregate
functions.

I am not an expert on the official sql spec, but I think it would be useful
if distinct on() could be used within an aggregate when supplied with
another column name. For example:

select sum(distinct on(id) order_price) from order_table;
select sum(distinct on(email_address) bounce_count) from email_bounces;
select sum(distinct on(county) area) from state_county_area;

Perhaps my examples are a bit silly. Realistically speaking, if I were
designing any of these hypothetical tables, I would design them so that the
"distinct on" wouldn't be necessary, as if it kind of redundant (in a
perfect database schema), but I have been thrown into very poorly designed
database schemas (has anyone ever had to develop for a firm that used the
"Interspire Email Marketer") where the "distinct on" syntax would have made
some monstrous queries a lot more compact and easy to maintain. Aside from
the benefits of making an unweildy database easier to navigate, I'm sure
there are other creative uses more "elogant".

At any rate, what I am suggesting may be inherently impossible or require
some major recoding, I'd still be curious to know.

Respond with thoughts, comments, concerns.

Radmilla

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-04-26 22:33:24 Re: Feature Request, aggregate functions distinct on
Previous Message Tom Lane 2011-04-26 22:00:29 Re: Partial Index [WHERE col IN ('A','B')] Planner Anomalies