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