GROUP BY

Returns queries in groups

SELECT SALES_DATE, SUM(TOTAL_AMOUNT)
FROM SALES
GROUP BY SALES_DATE
ORDER BY SALES_DATE;

This will take the sales made on the dates add them and return a column with the date -> total amount

Grouping Data using Multiple Columns

SELECT SALES_DATE, PRODUCT_ID, SUM(TOTAL_AMOUNT)
FROM SALES
WHERE SALES_DATE BETWEEN '01-JAN-15' AND '31-JAN-15'
GROUP BY SALES_DATE, PRODUCT_ID;

Ex: total sales volume for January group by day and product

Group by And HAVING

TOTAL SALES VOLUME FOR JANUARY BY AND AND PRODUCT AND TOTAL VOLUME GREATER THAN 1000

SELECT SALES_DATE, PRODUCT_ID, SUM(TOTAL_AMOUNT)
FROM SALES
WHERE SALES_DATE BETWEEN '01-JAN-15' AND '31-JAN-15'
GROUP BY SALES_DATE, PRODUCT_ID
HAVING SUM(TOTAL_AMOUNT) > 100;

GROUPING SETS

Enables performing multiple GROUP BY clauses with a single query.

SELECT name, category,
AVG(retail)
FROM publisher
JOIN books USING
(pubid)
GROUP BY GROUPING SETS
(name, category,
(name,category),());

CUBE

Performs aggregations for all possible combinations of columns included.

SELECT name, category,
AVG(retail)
FROM publisher
JOIN books USING
(pubid)
GROUP BY CUBE(name,
category)
ORDER BY name,
category;

ROLLUP

Performs increasing levels of cumulative subtotals, based on the provided column list.

SELECT name, category,
AVG(retail)
FROM publisher
JOIN books USING
(pubid)
GROUP BY ROLLUP(name,
category)
ORDER BY name,
category;

Back To Top- Home - Category