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 |
Subject: | Re: count and group by question |
Date: | 2002-06-19 23:00:25 |
Message-ID: | 1024527625.22814.259.camel@ryan.flowlabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dann Corbit | 2002-06-19 23:12:14 | Re: count and group by question |
Previous Message | Dann Corbit | 2002-06-19 22:31:43 | Re: count and group by question |