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.

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.

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: 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 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

-----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

-----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).

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

-----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)

Perfect! That's just what I needed!

Thanks so much

-r