Re: count and group by question - Mailing list pgsql-hackers

> -----Original Message-----
> From: Hannu Krosing [mailto:hannu@tm.ee]
> Sent: Wednesday, June 19, 2002 1:07 PM
> To: Dann Corbit
> Cc: ryan@paymentalliance.net; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] count and group by question
>
>
> On Thu, 2002-06-20 at 02:02, Dann Corbit wrote:
> > > -----Original Message-----
> > > From: ryan@paymentalliance.net [mailto:ryan@paymentalliance.net]
> > > Sent: Wednesday, June 19, 2002 12:19 PM
> > > To: pgsql-hackers@postgresql.org
> > > Subject: [HACKERS] count and group by question
> > >
> > >
> > > I have a query which contains both a group by and a count, e.g:
> > >
> > > SELECT
> > >    to_char(pa_shopping_cart.delivery_date, 'FMMM/FMDD/YY') AS
> > > delivery_date,
> > >    pa_products.product_name AS product_name,
> > >    pa_orders.order_state AS state,
> > >    count(*) AS count
> > > FROM
> > >   pa_shopping_cart,
> > >   pa_products,
> > >   pa_orders
> > > WHERE
> > >   pa_shopping_cart.order_id = pa_orders.order_id AND
> > >   pa_shopping_cart.product_id = pa_products.product_id
> > > GROUP BY
> > >   pa_shopping_cart.delivery_date,
> > >   pa_products.product_name,
> > >   pa_orders.order_state
> > > ORDER BY
> > >   pa_shopping_cart.delivery_date, pa_products.product_name;
> > >
> > >
> > > This query is really handy because it gives me the count of each
> > > product grouping by delivery within each possible order state.
> > >
> > > Here's the question - I would like to get the count of how
> > > many tuples are
> > > returned total.  With most queries, count(*) works great for
> > > this purpose,
> > > however I need something that will give me the total
> count of tuples
> > > returned even when there is a grouping.
> > >
> > > Any ideas?
> >
> > Run two queries, the second with no group by.
>
>
> Something like this should also work:
>
> SELECT
>    to_char(pa_shopping_cart.delivery_date, 'FMMM/FMDD/YY') AS
> delivery_date,
>    pa_products.product_name AS product_name,
>    pa_orders.order_state AS state,
>    count(*) AS count
> FROM
>   pa_shopping_cart,
>   pa_products,
>   pa_orders
> WHERE
>   pa_shopping_cart.order_id = pa_orders.order_id AND
>   pa_shopping_cart.product_id = pa_products.product_id
> GROUP BY
>   pa_shopping_cart.delivery_date,
>   pa_products.product_name,
>   pa_orders.order_state
>
> UNION
> SELECT
>    NULL,NULL,NULL, count
>  from (
> select count(*) AS count
> FROM
>   pa_shopping_cart,
>   pa_products,
>   pa_orders
> WHERE
>   pa_shopping_cart.order_id = pa_orders.order_id AND
>   pa_shopping_cart.product_id = pa_products.product_id
> ) total
>
> ORDER BY
>   pa_shopping_cart.delivery_date, pa_products.product_name;
>
> make the NULL,NULL,NULL part something else to get it sorted where you
> want.

Very clever.  I like it!  I'll have to remember that.

> > To make a really nice looking report with this kind of
> stuff, you can
> > use Crystal reports with the ODBC driver.  Then you can set as many
> > break columns as you like.
> >
> > Which reminds me, it would be nice to have the cube/rollup
> sort of OLAP
> > stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL:
>
> It seems like simple ROLLUP and () (i.e. grandTotal) would be
> doable by
> current executor and plans, i.e. sort and then aggregate,
> just add more
> aggregate fields and have different start/finalize conditions
>
> CUBE and GROUPING SETS will probably need another kind of execution
> plan, perhaps some kind of hashed tuple list.

Rollup can be simulated by a bunch of union all... Here is an example:
http://www.quest-pipelines.com/newsletter-v2/rollup.htm