Re: count and group by question

On Thu, 2002-06-20 at 04:00, Ryan Mahoney wrote:
> 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!

you could try:

select count(*) from (   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
) original_query



----------------
Hannu