Common rule to create or validate the GROUP BY clause

Level: Intermediate

Do you know that the content of the GROUP BY clause can be derived from the rest of the clauses in SELECT statement?

Well, it is true to a great extent. What can be derived is a combination of columns and expressions that must be included in GROUP BY.

We will start a demonstration with a simple puzzle:

Complete GROUP BY Clause in the following query:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno
GROUP BY _______________________________
HAVING e.job=’SALESMAN’
   AND SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

If you are not sure about the answer, learn the following rule.

Common rule to create or validate the GROUP BY clause:

It should at least include all columns and scalar expressions (constants, aggregate functions, and analytical functions are excluded) referenced in SELECT, HAVING, and ORDER BY clauses. Other columns can be specified as well, but they don’t have to.

Here is a solution to the above puzzle:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno
GROUP BY d.deptno, e.job, TO_CHAR(e.hiredate,'YYYY')
HAVING e.job=’SALESMAN’
   AND SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

Note that logically e.job=’SALESMAN’ condition does not belong to HAVING clause. If we push it to WHERE clause (where it belongs to), the group by clause may change:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno AND e.job=’SALESMAN’
GROUP BY d.deptno, TO_CHAR(e.hiredate,'YYYY')
HAVING SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

Since e.job is no longer in the HAVING clause, we don’t have to put it in GROUP BY, but we CAN. The Rule on GROUP BY clause says that at very least, the columns referenced in SELECT, HAVING, and ORDER BY clauses must be referenced in GROUP BY as well, but there might be other columns.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s