Re: count and group by question
Re: count and group by question
От:
"Rod Taylor" <rbt@zort.ca>
Дата:
Make the whole thing a subselect in the from, and count that. select count(*) from () as tab -- Rod ----- Original Message ----- From: "Ryan Mahoney" To: "Dann Corbit" Cc: "Hannu Krosing" ; 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) >
Re: count and group by question
От:
Hannu Krosing <hannu@tm.ee>
Дата:
On Thu, 2002-06-20 at 02:02, Dann Corbit wrote:
> > -----Original Message-----
> > From: ryan@paymentalliance.net [mailto:ryan@paymentalliance.net]
> > Sent: Wednesday, June 19, 2002 12:19 PM
> > To: pgsql-hackers@postgresql.org
> > Subject: [HACKERS] count and group by question
> >
> >
> > I have a query which contains both a group by and a count, e.g:
> >
> > 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
> > ORDER BY
> > pa_shopping_cart.delivery_date, pa_products.product_name;
> >
> >
> > This query is really handy because it gives me the count of each
> > product grouping by delivery within each possible order state.
> >
> > Here's the question - I would like to get the count of how
> > many tuples are
> > returned total. With most queries, count(*) works great for
> > this purpose,
> > however I need something that will give me the total count of tuples
> > returned even when there is a grouping.
> >
> > Any ideas?
>
> Run two queries, the second with no group by.
Something like this should also work:
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, countfrom (
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.
>
> To make a really nice looking report with this kind of stuff, you can
> use Crystal reports with the ODBC driver. Then you can set as many
> break columns as you like.
>
> Which reminds me, it would be nice to have the cube/rollup sort of OLAP
> stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL:
It seems like simple ROLLUP and () (i.e. grandTotal) would be doable by
current executor and plans, i.e. sort and then aggregate, just add more
aggregate fields and have different start/finalize conditions
CUBE and GROUPING SETS will probably need another kind of execution
plan, perhaps some kind of hashed tuple list.
> 7.9
> Function
> Specify a grouped table derived by the application of the clause> to the result of the
> previously specified clause.
> Format
> ::=
> GROUP BY
> ::=
>
> |
> |
> |
> |
> |
> ::=
> ROLLUP
> ::=
> CUBE
> ::=
> GROUPING SETS
> ::=
> [ { }... ]
> ::=
>
> ::=
>
> |
> |
> |
> ::=
>
> |
> ::=
> ::=
> [ { }...
> ]
> ::=
> [ ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Re: count and group by question
От:
Hannu Krosing <hannu@tm.ee>
Дата:
On Thu, 2002-06-20 at 03:15, Dann Corbit wrote: > > > Which reminds me, it would be nice to have the cube/rollup > > sort of OLAP > > > stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL: > > > > It seems like simple ROLLUP and () (i.e. grandTotal) would be > > doable by > > current executor and plans, i.e. sort and then aggregate, > > just add more > > aggregate fields and have different start/finalize conditions > > > > CUBE and GROUPING SETS will probably need another kind of execution > > plan, perhaps some kind of hashed tuple list. > > Rollup can be simulated by a bunch of union all... Here is an example: > http://www.quest-pipelines.com/newsletter-v2/rollup.htm I guess that all groupings are, just it would be much more efficient (not to mention simpler for user ;) if they could be done in one pass. But rewriting them to UNIONS seems a good stopgap solution. IIRC the OLAP supplement had also an option to tell wheather NULLS sort at the beginning or end. ------------------- Hannu
Re: count and group by question
От:
Hannu Krosing <hannu@tm.ee>
Дата:
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
Re: count and group by question
От:
Ryan Mahoney <ryan@paymentalliance.net>
Дата:
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.
Re: count and group by question
От:
Ryan Mahoney <ryan@paymentalliance.net>
Дата:
Perfect! That's just what I needed! Thanks so much -r > 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
Re: count and group by question
От:
"Dann Corbit" <DCorbit@connx.com>
Дата:
> -----Original Message-----
> From: ryan@paymentalliance.net [mailto:ryan@paymentalliance.net]
> Sent: Wednesday, June 19, 2002 12:19 PM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] count and group by question
>
>
> I have a query which contains both a group by and a count, e.g:
>
> 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
> ORDER BY
> pa_shopping_cart.delivery_date, pa_products.product_name;
>
>
> This query is really handy because it gives me the count of each
> product grouping by delivery within each possible order state.
>
> Here's the question - I would like to get the count of how
> many tuples are
> returned total. With most queries, count(*) works great for
> this purpose,
> however I need something that will give me the total count of tuples
> returned even when there is a grouping.
>
> Any ideas?
Run two queries, the second with no group by.
To make a really nice looking report with this kind of stuff, you can
use Crystal reports with the ODBC driver. Then you can set as many
break columns as you like.
Which reminds me, it would be nice to have the cube/rollup sort of OLAP
stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL:
7.9
Function
Specify a grouped table derived by the application of the to the result of the
previously specified clause.
Format
::=
GROUP BY
::=
|
|
|
|
|
::=
ROLLUP
::=
CUBE
::=
GROUPING SETS
::=
[ { }... ]
::=
::=
|
|
|
::=
|
::=
::=
[ { }...
]
::=
[ ]
Re: count and group by question
От:
"Dann Corbit" <DCorbit@connx.com>
Дата:
> -----Original Message----- > From: Hannu Krosing [mailto:hannu@tm.ee] > Sent: Wednesday, June 19, 2002 1:07 PM > To: Dann Corbit > Cc: ryan@paymentalliance.net; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] count and group by question > > > On Thu, 2002-06-20 at 02:02, Dann Corbit wrote: > > > -----Original Message----- > > > From: ryan@paymentalliance.net [mailto:ryan@paymentalliance.net] > > > Sent: Wednesday, June 19, 2002 12:19 PM > > > To: pgsql-hackers@postgresql.org > > > Subject: [HACKERS] count and group by question > > > > > > > > > I have a query which contains both a group by and a count, e.g: > > > > > > 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 > > > ORDER BY > > > pa_shopping_cart.delivery_date, pa_products.product_name; > > > > > > > > > This query is really handy because it gives me the count of each > > > product grouping by delivery within each possible order state. > > > > > > Here's the question - I would like to get the count of how > > > many tuples are > > > returned total. With most queries, count(*) works great for > > > this purpose, > > > however I need something that will give me the total > count of tuples > > > returned even when there is a grouping. > > > > > > Any ideas? > > > > Run two queries, the second with no group by. > > > Something like this should also work: > > 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. Very clever. I like it! I'll have to remember that. > > To make a really nice looking report with this kind of > stuff, you can > > use Crystal reports with the ODBC driver. Then you can set as many > > break columns as you like. > > > > Which reminds me, it would be nice to have the cube/rollup > sort of OLAP > > stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL: > > It seems like simple ROLLUP and () (i.e. grandTotal) would be > doable by > current executor and plans, i.e. sort and then aggregate, > just add more > aggregate fields and have different start/finalize conditions > > CUBE and GROUPING SETS will probably need another kind of execution > plan, perhaps some kind of hashed tuple list. Rollup can be simulated by a bunch of union all... Here is an example: http://www.quest-pipelines.com/newsletter-v2/rollup.htm
Re: count and group by question
От:
"Dann Corbit" <DCorbit@connx.com>
Дата:
> -----Original Message----- > From: Hannu Krosing [mailto:hannu@tm.ee] > Sent: Wednesday, June 19, 2002 1:25 PM > To: Dann Corbit > Cc: ryan@paymentalliance.net; pgsql-hackers@postgresql.org > Subject: RE: [HACKERS] count and group by question > > > On Thu, 2002-06-20 at 03:15, Dann Corbit wrote: > > > > Which reminds me, it would be nice to have the cube/rollup > > > sort of OLAP > > > > stuff from SQL99 ISO/IEC 9075-2:1999 (E) in PostgreSQL: > > > > > > It seems like simple ROLLUP and () (i.e. grandTotal) would be > > > doable by > > > current executor and plans, i.e. sort and then aggregate, > > > just add more > > > aggregate fields and have different start/finalize conditions > > > > > > CUBE and GROUPING SETS will probably need another kind of > execution > > > plan, perhaps some kind of hashed tuple list. > > > > Rollup can be simulated by a bunch of union all... Here is > an example: > > http://www.quest-pipelines.com/newsletter-v2/rollup.htm > > I guess that all groupings are, just it would be much more efficient > (not to mention simpler for user ;) if they could be done in one pass. > > But rewriting them to UNIONS seems a good stopgap solution. Yes. It was to show the concept (not to you). Obviously, it would be much better to do it correctly internally (which is why I asked for the feature). > IIRC the OLAP supplement had also an option to tell wheather > NULLS sort > at the beginning or end.
Re: count and group by question
От:
"Dann Corbit" <DCorbit@connx.com>
Дата:
> -----Original Message----- > From: Ryan Mahoney [mailto:ryan@paymentalliance.net] > Sent: Wednesday, June 19, 2002 4:00 PM > To: Dann Corbit > Cc: Hannu Krosing; pgsql-hackers@postgresql.org > 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! Warning -- totally untested and glommed from memory -- probably not quite right... SELECT count (distinct cast(to_char(pa_shopping_cart.delivery_date, 'FMMM/FMDD/YY') as varchar) || pa_products.product_name || pa_orders.order_state) 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
count and group by question
От:
<ryan@paymentalliance.net>
Дата:
I have a query which contains both a group by and a count, e.g: 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 ORDER BY pa_shopping_cart.delivery_date, pa_products.product_name; This query is really handy because it gives me the count of each product grouping by delivery within each possible order state. Here's the question - I would like to get the count of how many tuples are returned total. With most queries, count(*) works great for this purpose, however I need something that will give me the total count of tuples returned even when there is a grouping. Any ideas? Ryan Mahoney
