Rollup data hierarchy in postgresql 8.0?

Question

I am trying to rollup in postgresql 8.0. In latest version on postgresql we have ROLLUP function, but how to implement rollup in postgresql 8.0 ? Anyone have experience with the same?

I tried the below

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );

But getting the following error:

42883: function rollup( integer, integer, integer) does not exist

followed from http://www.postgresqltutorial.com/postgresql-rollup/

Answer 1

As GROUP BY ROLLUP was introduced with version 9.5, the query has no chance to work. But if you think about what it does it should be very easy in your case to come up with a version producing the same result.

Basically, you want to have:

1. an overall sum
2. a sum per year
3. and a sum per month
4. for the daily counts

I've written the above in a special way, so that it becomes clear what you actually need:

1. produce daily counts
2. generate sum per month from daily counts
3. generate sum per year from monthly sums or daily counts
4. generate total from yearly sums, monthly sums or daily counts
5. UNION ALL of the above in the order you want

As the default for GROUP BY ROLLUP is to write-out the total first and then the individual grouping sets with NULLS LAST, the following query will do the same:

WITH
    daily AS (
        SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, EXTRACT (DAY FROM rental_date) d, COUNT (rental_id) AS count
        FROM rental
        GROUP BY 1, 2, 3
    ),
    monthly AS (
        SELECT y, M, NULL::double precision d, SUM (count) AS count
        FROM daily
        GROUP BY 1, 2
    ),
    yearly AS (
        SELECT y, NULL::double precision M, NULL::double precision d, SUM (count) AS count
        FROM monthly
        GROUP BY 1
    ),
    totals AS (
        SELECT NULL::double precision y, NULL::double precision M, NULL::double precision d, SUM (count) AS count
        FROM yearly
    )
SELECT * FROM totals
UNION ALL
SELECT * FROM daily
UNION ALL
SELECT * FROM monthly
UNION ALL
SELECT * FROM yearly
;

The above works with PostgreSQL 8.4+. If you don't even have that version, we must fall back to the old-school UNION without re-using aggregation data:

SELECT NULL::double precision y, NULL::double precision M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, EXTRACT (DAY FROM rental_date) d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1, 2, 3
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, EXTRACT (MONTH FROM rental_date) M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1, 2
UNION ALL
SELECT EXTRACT (YEAR FROM rental_date) y, NULL::double precision M, NULL::double precision d, COUNT (rental_id) AS count
FROM rental
GROUP BY 1
;