Re: Grouping Sets

Paul,

I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
results match. Nice.

But, when trying to calculate some percentages and control some rounding,
the results are coming back as null for some reason.  I have tried casting,
to_char, etc to try to get them to show up..no love ensued. I was wondering
if you have any idea what could by happening. I have attached some test
results based on the grouping sets wiki. One of the examples is just using
group by, as a sanity check.  Any ideas or help would be much appreciated.


CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
sales real, cost real );

insert into cars2 values('skoda', 'czech rep.', 10000, 8000);
insert into cars2 values('skoda', 'germany', 5000, 6000);
insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
insert into cars2 values('bmw', 'germany', 18000, 15000);
insert into cars2 values('opel', 'czech rep.', 7000, 5000);
insert into cars2 values('opel', 'germany', 7000, 5000);

--grouping sets test--

select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by rollup(name, place);


name    place    sales    cost    cost_sales_ratio    cost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw    czech rep.    6000    4000    0.6667    (null)    (null)
skoda    germany    5000    6000    1.2    (null)    (null)
opel    czech rep.    7000    5000    0.7143    (null)    (null)
opel    germany    7000    5000    0.7143    (null)    (null)
skoda    czech rep.    10000    8000    0.8    (null)    (null)
bmw    germany    18000    15000    0.8333    (null)    (null)
bmw    (null)    24000    19000    0.7917    (null)    (null)
skoda    (null)    15000    14000    0.9333    (null)    (null)
opel    (null)    14000    10000    0.7143    (null)    (null)
(null)    (null)    53000    43000    0.8113    (null)    (null)

--group by sanity test--

select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by name, place;

name    place    sales    cost    cost_sales_ratio    cost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw    czech rep.    6000    4000    0.6667    66.6667    67
skoda    germany    5000    6000    1.2    120    120
opel    czech rep.    7000    5000    0.7143    71.4286    71
opel    germany    7000    5000    0.7143    71.4286    71
skoda    czech rep.    10000    8000    0.8    80    80
bmw    germany    18000    15000    0.8333    83.3333    83



Thanks




--
Regards

David

-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Sunday, September 18, 2011 2:34 PM
To: David Rinaldi
Subject: Re: [HACKERS] Grouping Sets

Hello

A last patch should be applied on 8.4 or 9.0 - should to try it. I
worked with developer version.

http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php

Regards

Pavel Stehule

2011/9/18 David Rinaldi <edwbroker@gmail.com>:
> Hi,
>
> I tried to apply the Grouping Sets Patch to 8.4, but received several
Hunks
> failed messages, does anyone know if the failing hunks can be applied
> manually?  Or what version they were applied to specifically?
>
> --
> Regards
>
> David
>