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.

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

> 7.9 <group by clause>
> Function
> Specify a grouped table derived by the application of the <group by
> clause> to the result of the
> previously specified clause.
> Format
> <group by clause> ::=
> GROUP BY <grouping specification>
> <grouping specification> ::=
> <grouping column reference>
> | <rollup list>
> | <cube list>
> | <grouping sets list>
> | <grand total>
> | <concatenated grouping>
> <rollup list> ::=
> ROLLUP <left paren> <grouping column reference list> <right paren>
> <cube list> ::=
> CUBE <left paren> <grouping column reference list> <right paren>
> <grouping sets list> ::=
> GROUPING SETS <left paren> <grouping set list> <right paren>
> <grouping set list> ::=
> <grouping set> [ { <comma> <grouping set> }... ]
> <concatenated grouping> ::=
> <grouping set> <comma> <grouping set list>
> <grouping set> ::=
> <ordinary grouping set>
> | <rollup list>
> | <cube list>
> | <grand total>
> <ordinary grouping set> ::=
> <grouping column reference>
> | <left paren> <grouping column reference list> <right paren>
> <grand total> ::= <left paren> <right paren>
> <grouping column reference list> ::=
> <grouping column reference> [ { <comma> <grouping column reference> }...
> ]
> <grouping column reference> ::=
> <column reference> [ <collate clause> ]
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



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

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:

7.9 <group by clause>
Function
Specify a grouped table derived by the application of the <group by
clause> to the result of the
previously specified clause.
Format
<group by clause> ::=
GROUP BY <grouping specification>
<grouping specification> ::=
<grouping column reference>
| <rollup list>
| <cube list>
| <grouping sets list>
| <grand total>
| <concatenated grouping>
<rollup list> ::=
ROLLUP <left paren> <grouping column reference list> <right paren>
<cube list> ::=
CUBE <left paren> <grouping column reference list> <right paren>
<grouping sets list> ::=
GROUPING SETS <left paren> <grouping set list> <right paren>
<grouping set list> ::=
<grouping set> [ { <comma> <grouping set> }... ]
<concatenated grouping> ::=
<grouping set> <comma> <grouping set list>
<grouping set> ::=
<ordinary grouping set>
| <rollup list>
| <cube list>
| <grand total>
<ordinary grouping set> ::=
<grouping column reference>
| <left paren> <grouping column reference list> <right paren>
<grand total> ::= <left paren> <right paren>
<grouping column reference list> ::=
<grouping column reference> [ { <comma> <grouping column reference> }...
]
<grouping column reference> ::=
<column reference> [ <collate clause> ]

Wed, 19 Jun 2002 14:02:17 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: count and group by question