GROUP BY

Group functions require that any column listed in the SELECT CLAUSE that is not part of a group function must be listed in GROUP BY CLAUSE

• BELOW QUERY GENERATES AN ERROR - NOT A GROUP BY EXPRESSION -

SELECT JOB_ID, LASTNAME, AVG(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID

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 ALL EMPLOYEES BY DEPARTMENT, THEN, WITHIN EACH DEPARTMENT, GROUP THEM BY JOB

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID > 40
GROUP BY DEPARTMENT_ID, JOB_ID;

THE WHERE CLAUSE IS USED TO RESTRICT ROWS; THE HAVING CLAUSE IS USED TO RESTRICT GROUPS RETURNED BY A GROUP BY CLAUSE

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;

generate a list of each book category along with the average profit, as you did earlier. This query needs to include the following data filters:

• Show only book categories with an average profit greater than $15.00.
• Include only the categories Computer, Children, and Business.

The first filtering task should be done with a HAVING clause because it places a condition on an aggregated value. The second filtering task should be handled with a WHERE clause at the row level to include only the book rows in the specified categories before the aggregation is performed.

SELECT catcode, TO_CHAR(AVG(retail-cost), '999,99') "Profit"
FROM books
WHERE catcode IN('COM', 'CHN', 'BUS')
GROUP BY catcode
HAVING AVG(retail-cost) > 15

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),());

SELECT name, catcode,
COUNT (isbn), TO_CHAR(AVG(retail), '999.99') "Avg Retail"
FROM publisher JOIN books  USING(pubid)
WHERE pubid IN (2,3,5)
GROUP BY GROUPING SETS(name, catcode,(name, catcode),())

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

ROOLUP CREATES SUBTOTALS THAT ROLL UP FROM THE MOST DETAILED LEVEL TO A GRAND TOTAL, USING THE GROUPING LIST SPECIFIED IN THE GROUP BY CLAUSE

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