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
