Thread: Feature Request, aggregate functions distinct on
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
luda posch <ludaludaluda@gmail.com> writes:
> 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;
Use another level of sub-select, eg
select sum(order_price) from
(select distinct on(id) order_price from order_table ...) ss;
The usefulness of this doesn't really seem high enough to justify
inventing and maintaining a more compact way to handle it.
regards, tom lane
On 04/26/2011 04:33 PM, Tom Lane wrote: > luda posch<ludaludaluda@gmail.com> writes: >> 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; > > Use another level of sub-select, eg > > select sum(order_price) from > (select distinct on(id) order_price from order_table ...) ss; > > The usefulness of this doesn't really seem high enough to justify > inventing and maintaining a more compact way to handle it. > > regards, tom lane > I thing OP was hoping for tuples of email, bounceCount. I don't read your sub-select as providing this?