Re: count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 10:43:25 -0800
David Fetter <david(at)fetter(dot)org> wrote:

> On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo
> wrote:
> > I noticed that starting from 8.2 the documentation at
> > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html
> > say that multiple distinct expressions are supported
> >
> > aggregate_name (DISTINCT expression [, expression] )

> In 8.4, you'll be able to do:

> WITH d AS (
> SELECT DISTINCT c1, c2 FROM table1
> )
> SELECT count(*) FROM d;

Nice, but what will be the difference from
select count(*) from (select distinct c1, c2 from t);
?
Optimisation?

Furthermore... I was actually looking at docs because I needed to
find a way supported by both postgresql and mysql and I've heard
that mysql is not that good at subselect and I doubt it supports
WITH AS. (OK not really a postgresql problem...).

Meanwhile what would you suggest as a general approach to stuff like

select count(distinct c1, c2) from t;

regardless of mysql support?
and considering mysql support?

I was thinking to find some way to exploit group by, but I didn't
come to anything useful yet.

> and very likely an OLAP version. :)

What's "an OLAP version" of WITH d AS...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it