Using UNION and GROUP BY WITH ROLLUP for two separate queries

I am querying current and archived invoices and using UNION to combine the results into one table. I’m using GROUP BY to control how the individual invoice lines (ii and iai) are summed.
I then need to add in subtotals for groups of the unified data, so have tried to use a GROUP BY WITH ROLLUP.

SELECT * FROM(
    SELECT ih.ref, ih.invno, SUM(ii.val) AS 'val_'
    FROM ih, sl, ii
    WHERE ii.invno = ih.invno AND ih.ref = sl.ref
    GROUP BY ii.invno   

    UNION
    SELECT iha.ref, iha.invno, SUM(iai.val) AS 'val_'
    FROM iha, sl, iai
    WHERE iai.invno = iha.invno AND iha.ref = sl.ref
    GROUP BY iai.invno
)AS A
GROUP BY 1,2 WITH ROLLUP

The problem is that the resulting data isn’t correctly totalling values on the aggregate lines.

The val_ column isn’t showing a subtotal in the NULL rows created from the ROLLUP. This works if I only query with the one select statement of invoices, instead of querying 2 with a union.
It’s clearly an issue due to using both UNION and GROUP BY WITH ROLLUP, but how can I query both data sets, with summed figures, and still get aggregate rows with totalled values?

>Solution :

you can use a workaround by creating separate subqueries for each individual invoice query before combining them with UNION. Then, you can use GROUP BY WITH ROLLUP on the combined subqueries to get the correct subtotals.
Here’s how you can modify your query:

  SELECT ref, invno, SUM(val_) AS 'val_'
   FROM (
SELECT ih.ref, ih.invno, SUM(ii.val) AS 'val_'
FROM ih, sl, ii
WHERE ii.invno = ih.invno AND ih.ref = sl.ref
GROUP BY ih.ref, ih.invno   

UNION

SELECT iha.ref, iha.invno, SUM(iai.val) AS 'val_'
FROM iha, sl, iai
WHERE iai.invno = iha.invno AND iha.ref = sl.ref
GROUP BY iha.ref, iha.invno
        ) AS A
        GROUP BY ref, invno WITH ROLLUP;

With this modification, the GROUP BY WITH ROLLUP will generate the subtotal rows as expected, giving you the correct totalled values for the combined data set. The ROLLUP will produce subtotals for each ‘ref’ and also a grand total for the entire result set.