Database Reference
In-Depth Information
GROUP BY o.product_id
ORDER BY SUM(o.item_quantity) DESC
LIMIT 3)
GROUP BY CUBE( r.product_id, DATE_PART('year',
r.order_datetime) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime)
product_id year total group_id group_year
15060 2013 5996 0 0
15060 2014 57 0 0
15060 6053 0 1
15066 2013 6030 0 0
15066 2014 52 0 0
15066 6082 0 1
15072 2013 6023 0 0
15072 2014 66 0 0
15072 6089 0 1
2013 18049 1 0
2014 175 1 0
18224 1 1
In the preceding query,
group_year
is set to 1 when a total is calculated across
the values of
year
. Similarly,
group_id
is set to 1 when a total is calculated across
the values of
product_id
.
The functionality of
ROLLUP
and
CUBE
can be customized via
GROUPING SETS
.
The SQL query using the
CUBE
operator can be replaced with the following query
that employs
GROUPING SETS
to provide the same results.
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,
