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

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, countfrom (
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