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.