On Thu, 2002-06-20 at 03:15, Dann Corbit wrote:
> > > 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

I guess that all groupings are, just it would be much more efficient
(not to mention simpler for user ;) if they could be done in one pass.

But rewriting them to UNIONS seems a good stopgap solution.

IIRC the OLAP supplement had also an option to tell wheather NULLS sort
at the beginning or end.

-------------------
Hannu



20 Jun 2002 01:24:46 +0500
Hannu Krosing <hannu@tm.ee>
Re: count and group by question
On Thu, 2002-06-20 at 04:00, Ryan Mahoney wrote:
> OK, so I tried both queries but they don't meet my requirement, I think
> I wasn't clear.  The methods suggested both return the aggregate count
> as if the rows had not been grouped.  What I am looking for is a count
> of how many rows were returned *with* the grouping.
> 
> So, suppose there are 1000 orders total, but when grouped by product 200
> rows are returned.  I am trying to find a way to get that 200 not the
> original 1000 count.
> 
> Does this make sense?  The Union was really interesting, I haven't used
> union very much - but I will now!

you could try:

select count(*) from (
    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
) original_query



----------------
Hannu



20 Jun 2002 02:20:17 +0500
Hannu Krosing <hannu@tm.ee>
Re: count and group by question
> -----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


Wed, 19 Jun 2002 15:15:49 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: count and group by question
OK, so I tried both queries but they don't meet my requirement, I think
I wasn't clear.  The methods suggested both return the aggregate count
as if the rows had not been grouped.  What I am looking for is a count
of how many rows were returned *with* the grouping.

So, suppose there are 1000 orders total, but when grouped by product 200
rows are returned.  I am trying to find a way to get that 200 not the
original 1000 count.

Does this make sense?  The Union was really interesting, I haven't used
union very much - but I will now!

Thanks for your suggestions!

-r
 
> > 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.


19 Jun 2002 19:00:25 -0400
Ryan Mahoney <ryan@paymentalliance.net>
Re: count and group by question
Make the whole thing a subselect in the from, and count that.

select count(*)
from (<other query>) as tab
--
Rod
----- Original Message -----
From: "Ryan Mahoney" <ryan@paymentalliance.net>
To: "Dann Corbit" <DCorbit@connx.com>
Cc: "Hannu Krosing" <hannu@tm.ee>; <pgsql-hackers@postgresql.org>
Sent: Wednesday, June 19, 2002 7:00 PM
Subject: Re: [HACKERS] count and group by question


> OK, so I tried both queries but they don't meet my requirement, I
think
> I wasn't clear.  The methods suggested both return the aggregate
count
> as if the rows had not been grouped.  What I am looking for is a
count
> of how many rows were returned *with* the grouping.
>
> So, suppose there are 1000 orders total, but when grouped by product
200
> rows are returned.  I am trying to find a way to get that 200 not
the
> original 1000 count.
>
> Does this make sense?  The Union was really interesting, I haven't
used
> union very much - but I will now!
>
> Thanks for your suggestions!
>
> -r
>
> > > 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.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>


Wed, 19 Jun 2002 19:20:37 -0400
"Rod Taylor" <rbt@zort.ca>
Re: count and group by question
Perfect!  That's just what I needed!

Thanks so much

-r

> select count(*) from (
>     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
> ) original_query
> 
> 
> 
> ----------------
> Hannu


19 Jun 2002 22:07:50 -0400
Ryan Mahoney <ryan@paymentalliance.net>
Re: count and group by question