Grouping sets with window aggregates

You can use window aggregates in grouping selects, as in the following example:

SELECT grp, COUNT(*), sum(COUNT(*)) OVER (ORDER BY COUNT(*) rows 
unbounded preceding) FROM emp GROUP BY grp;

Such a query is handled by converting it to a window aggregating query done on a subquery that is doing the grouped aggregates. So the example becomes the following:

SELECT grp, ct, SUM(ct OVER (ORDER BY ct rows unbounded preceding) FROM 
(SELECT grp, COUNT(*) AS ct FROM emp GROUP BY grp) as s;
 GRP      | COUNT | SUM 
----------+-------+-----
 gone     |     1 |   1
 dev      |     1 |   2
 mkt      |     1 |   3
 hdev     |     1 |   4
 adm      |     3 |   7
 sdev     |     7 |  14

So you can use enhanced group by options in combination with window aggregates, as in the following example:

SELECT grp, COUNT(*) AS grpcnt, SUM(COUNT(*)) OVER (ORDER BY COUNT(*) 
rows unbounded preceding) FROM emp GROUP BY ROLLUP(grp);

This becomes the following:

SELECT grp, ct, SUM(ct OVER (ORDER BY ct rows unbounded preceding) FROM 
(SELECT grp, COUNT(*) AS ct FROM emp GROUP BY ROLLUP(grp)) AS s;
 GRP      | COUNT | SUM 
----------+-------+-----
 gone     |     1 |   1
 dev      |     1 |   2
 mkt      |     1 |   3
 hdev     |     1 |   4
 adm      |     3 |   7
 sdev     |     7 |  14
          |    14 |  28