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(at)paymentalliance(dot)net>
To: "Dann Corbit" <DCorbit(at)connx(dot)com>
Cc: "Hannu Krosing" <hannu(at)tm(dot)ee>; <pgsql-hackers(at)postgresql(dot)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(at)postgresql(dot)org)
>