Thread: 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? Ryan Mahoney
> -----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 <group by clause>
Function
Specify a grouped table derived by the application of the <group by
clause> to the result of the
previously specified clause.
Format
<group by clause> ::=
GROUP BY <grouping specification>
<grouping specification> ::=
<grouping column reference>
| <rollup list>
| <cube list>
| <grouping sets list>
| <grand total>
| <concatenated grouping>
<rollup list> ::=
ROLLUP <left paren> <grouping column reference list> <right paren>
<cube list> ::=
CUBE <left paren> <grouping column reference list> <right paren>
<grouping sets list> ::=
GROUPING SETS <left paren> <grouping set list> <right paren>
<grouping set list> ::=
<grouping set> [ { <comma> <grouping set> }... ]
<concatenated grouping> ::=
<grouping set> <comma> <grouping set list>
<grouping set> ::=
<ordinary grouping set>
| <rollup list>
| <cube list>
| <grand total>
<ordinary grouping set> ::=
<grouping column reference>
| <left paren> <grouping column reference list> <right paren>
<grand total> ::= <left paren> <right paren>
<grouping column reference list> ::=
<grouping column reference> [ { <comma> <grouping column reference> }...
]
<grouping column reference> ::=
<column reference> [ <collate clause> ]
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 <group by clause>
> Function
> Specify a grouped table derived by the application of the <group by
> clause> to the result of the
> previously specified clause.
> Format
> <group by clause> ::=
> GROUP BY <grouping specification>
> <grouping specification> ::=
> <grouping column reference>
> | <rollup list>
> | <cube list>
> | <grouping sets list>
> | <grand total>
> | <concatenated grouping>
> <rollup list> ::=
> ROLLUP <left paren> <grouping column reference list> <right paren>
> <cube list> ::=
> CUBE <left paren> <grouping column reference list> <right paren>
> <grouping sets list> ::=
> GROUPING SETS <left paren> <grouping set list> <right paren>
> <grouping set list> ::=
> <grouping set> [ { <comma> <grouping set> }... ]
> <concatenated grouping> ::=
> <grouping set> <comma> <grouping set list>
> <grouping set> ::=
> <ordinary grouping set>
> | <rollup list>
> | <cube list>
> | <grand total>
> <ordinary grouping set> ::=
> <grouping column reference>
> | <left paren> <grouping column reference list> <right paren>
> <grand total> ::= <left paren> <right paren>
> <grouping column reference list> ::=
> <grouping column reference> [ { <comma> <grouping column reference> }...
> ]
> <grouping column reference> ::=
> <column reference> [ <collate clause> ]
>
> ---------------------------(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
> -----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
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
> -----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.
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.
> -----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
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@paymentalliance.net> To: "Dann Corbit" <DCorbit@connx.com> Cc: "Hannu Krosing" <hannu@tm.ee>; <pgsql-hackers@postgresql.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@postgresql.org) >
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_nameAS 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
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