Re: count and group by question

On Thu, 2002-06-20 at 02:02, Dann Corbit wrote:
> > -----Original Message-----
> > From: ryan(at)paymentalliance(dot)net [mailto:ryan(at)paymentalliance(dot)net]
> > Sent: Wednesday, June 19, 2002 12:19 PM
> > To: pgsql-hackers(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly