Re: count and group by question

Make the whole thing a subselect in the from, and count that.

select count(*)
from (<other query>) as tab
--
Rod
----- Original Message -----
From: "Ryan Mahoney" <ryan@paymentalliance.net>
To: "Dann Corbit" <DCorbit@connx.com>
Cc: "Hannu Krosing" <hannu@tm.ee>; <pgsql-hackers@postgresql.org>
Sent: Wednesday, June 19, 2002 7:00 PM
Subject: Re: [HACKERS] 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.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>