From: | "Rod Taylor" <rbt(at)zort(dot)ca> |
---|---|
To: | "Ryan Mahoney" <ryan(at)paymentalliance(dot)net>, "Dann Corbit" <DCorbit(at)connx(dot)com> |
Cc: | "Hannu Krosing" <hannu(at)tm(dot)ee>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: count and group by question |
Date: | 2002-06-19 23:20:37 |
Message-ID: | 03ac01c217e7$ebbbe8c0$fe01a8c0@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2002-06-19 23:52:38 | Re: [Fwd: [PATCHES] contrib/showguc (was Re: revised sample |
Previous Message | Dann Corbit | 2002-06-19 23:12:14 | Re: count and group by question |