FW: Table Join (Maybe?) - Mailing list pgsql-sql

Hi,

Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. I'm not a Postgres expert, but I think we can try the following alternative approach :

Sample data for the two tables given by you are created as :

Table : stores
code     name         bms     state         business_open
1        Customer1    Y        State1        Y
2        Customer2    Y        State2        Y
3        Customer3    Y        State1        Y
4        Customer4    Y        State2        Y

Table : sales_figures
rep_date     store     sales_value     sales_customers
2006-01-01    1        1000            0
2006-01-02    1        1000            0
2006-02-01    2        200                0
2006-02-02    2        200                0
2006-02-03    2        500                0
2006-01-03    1        100                0
2006-01-01    3        300                0
2006-01-02    3        200                0
2006-02-01    4        700                0
2006-02-02    4        400                0

If I execute the following query :
SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE a.code = b.store GROUP BY a.state, a.name
UNION
SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE a.code = b.store GROUP BY a.state
UNION
SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, sales_figures b WHERE a.code =
b.store ORDER BY 2

I get the following output (same as what you expect?) :

name        state    sum

Customer1    State1    2100
Customer3    State1    500
NULL        State1    2600
Customer2    State2    900
Customer4    State2    1100
NULL        State2    2000
GRAND TOTAL    NULL    4600

Is the above approach OK?

Regards
Arul