From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Ryan Mahoney <ryan(at)paymentalliance(dot)net> |
Cc: | Dann Corbit <DCorbit(at)connx(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: count and group by question |
Date: | 2002-06-19 21:20:17 |
Message-ID: | 1024521618.2191.31.camel@rh72.home.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dann Corbit | 2002-06-19 22:15:49 | Re: count and group by question |
Previous Message | Peter Eisentraut | 2002-06-19 21:13:10 | Re: [Fwd: [PATCHES] contrib/showguc (was Re: revised sample |