Database Reference
In-Depth Information
DATE_PART('year', r.order_datetime) ),
( r.product_id ),
( DATE_PART('year', r.order_datetime) ),
( ) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime)
The listed grouping sets define the columns for which subtotals will be provided.
The last grouping set,
( )
, specifies that the overall total is supplied in the query
results. For example, if only the grand total was desired, the following SQL query
using
GROUPING SETS
could be used.
SELECT r.product_id,
DATE_PART('year', r.order_datetime) AS year,
SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
FROM orders_recent o
GROUP BY o.product_id
ORDER BY SUM(o.item_quantity) DESC
LIMIT 3)
GROUP BY GROUPING SETS( ( r.product_id,
DATE_PART('year', r.order_datetime) ),
( ) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime)
product_id year total
15060 2013 5996
15060 2014 57
15066 2013 6030
15066 2014 52
15072 2013 6023
15072 2014 66
18224
Because the
GROUP BY
clause can contain multiple
CUBE
,
ROLLUP
, or column
specifications, duplicate grouping sets might occur. The
GROUP_ID()
function
identifies the unique rows with a 0 and the redundant rows with a 1, 2, .… To
