Oracle GROUP BY and HAVING Clauses

Oracle GROUP BY & HAVING Clauses Version 11.2.0.3

Basic Group By

GROUP BY is used in conjunction with aggregating functions to group the results by the unaggregated columns

Aggregating Functions

For more use the link to Aggregating Functions at the bottom of the page

AVG	LAST	STDDEV
COUNT	MAX	SUM
FIRST	MIN	VARIANCE

SQL Statement Not Requiring GROUP BY

SELECT COUNT(*)
FROM all_tables;

SQL Statement With A Single Ungrouped Column Requiring GROUP BY

SELECT table_name, COUNT(*)
FROM all_tables;

SELECT table_name, COUNT(*)
FROM all_indexes
GROUP BY table_name;

SQL Statement With Multiple Ungrouped Columns And Multiple Grouped Columns

set linesize 121
col index_type format a20

SELECT table_name, index_type, COUNT(leaf_blocks), COUNT(distinct_keys)
FROM all_indexes
GROUP BY table_name, index_type;

Group By Result Merging

The following demo is based on a demo posted by Michel Cadot in comp.databases.oracle.server

conn uwclass/uwclass

CREATE TABLE grp_test (
year VARCHAR2(4),
result NUMBER(7));

INSERT INTO grp_test VALUES ('2011', 1000);
INSERT INTO grp_test VALUES ('2008', 1250);
INSERT INTO grp_test VALUES ('2009', 3786);
INSERT INTO grp_test VALUES ('2010', 977);
INSERT INTO grp_test VALUES ('2007', 5000);
INSERT INTO grp_test VALUES ('2001', 123);
INSERT INTO grp_test VALUES ('2008', 125);
INSERT INTO grp_test VALUES ('2003', 2000);
INSERT INTO grp_test VALUES ('2004', 2200);
INSERT INTO grp_test VALUES ('2003', 150);
COMMIT;

SELECT * FROM grp_test;

SELECT DECODE(SIGN(year-2008),1,year,'2008 or before'), SUM(result)
FROM grp_test
GROUP BY DECODE(SIGN(year-2008),1,year,'2008 or before')
ORDER BY 1;

Group By With Having

HAVING is used to perform an action on groups created by GROUP BY similar to that of the WHERE clause on rows in a basic SQL statement. The WHERE clause limits the rows evaluated. The HAVING clause limits the grouped rows returned.

GROUP BY With HAVING Clause

SELECT table_name, COUNT(*)
FROM all_indexes
GROUP BY table_name
HAVING COUNT(*) > 1;

SELECT table_name, COUNT(*)
FROM all_indexes
GROUP BY table_name
HAVING COUNT(*) = 2;

SELECT table_name, COUNT(*)
FROM all_indexes
GROUP BY table_name
HAVING COUNT(*) BETWEEN 2 AND 3;

Group By Substitute

GROUP BY Substitution Demo

conn uwclass/uwclass

CREATE TABLE t1 AS
SELECT rownum objid, 'name'||rownum objname
FROM all_objects;

CREATE TABLE t2 AS
SELECT objid, dbms_random.value(1,10) PRICE
FROM t1;

INSERT INTO t2
SELECT objid, dbms_random.value(1,10) PRICE
FROM t1;

CREATE INDEX t1_idx
ON t1 (objid);

CREATE INDEX t2_idx
ON t2(objid);

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);

-- observe the output

SELECT t1.objid, MAX(t2.price) PRICE
FROM t1, t2
WHERE t1.objid = t2.objid
AND t1.objname = 'name120'
GROUP BY t1.objid;

SELECT t1.objid, (
SELECT MAX(price)
FROM t2
WHERE t2.objid = t1.objid) PRICE
FROM t1
WHERE objname = 'name120';

-- compare the plans

set autotrace traceonly exp statistics

SELECT t1.objid, MAX(t2.price) PRICE
FROM t1, t2
WHERE t1.objid = t2.objid
AND t1.objname = 'name120'
GROUP BY t1.objid;

SELECT t1.objid, (
SELECT MAX(price)
FROM t2
WHERE t2.objid = t1.objid) PRICE
FROM t1
WHERE objname = 'name120';

set autotrace off

More Than One Way To Approach A Problem

conn / as sysdba

set autotrace traceonly

-- conventional group by
SELECT table_name, COUNT(*) TAB_CNT
FROM all_indexes
GROUP BY table_name;

-- sub-query solution
SELECT DISTINCT table_name, (
SELECT COUNT(*)
FROM all_indexes ai2
WHERE ai2.table_name = ai1.table_name) TAB_CNT
FROM all_indexes ai1;

-- analytic counterpart
SELECT DISTINCT table_name, COUNT(*)
OVER (PARTITION BY table_name) TAB_CNT
FROM all_indexes;

set autotrace off

-- Note: this demo does not take into account the possibility that indexes with the same name occur in more than one schema

Find the most prevalent value in a column

SELECT cnt1.initial_extent
FROM (
SELECT initial_extent, COUNT(*) TOTAL
FROM all_tables
GROUP BY initial_extent) cnt1,
     (
SELECT MAX(total) MAXTOTAL
FROM (
    SELECT initial_extent, COUNT(*) TOTAL
    FROM all_tables
    GROUP BY initial_extent)) cnt2
WHERE cnt1.total = cnt2.maxtotal;

Duplicate Removal

Remove Duplicate Data

conn uwclass/uwclass

CREATE TABLE t (
col1 VARCHAR2(3),
col2 VARCHAR2(3));

INSERT INTO t VALUES ('AAA', '123');
INSERT INTO t VALUES ('BBB', '123');
INSERT INTO t VALUES ('CCC', '789');
INSERT INTO t VALUES ('AAA', '123');
INSERT INTO t VALUES ('DDD', '123');
INSERT INTO t VALUES ('CCC', '789');
INSERT INTO t VALUES ('CCC', '987');
COMMIT;

SELECT * FROM t;

SELECT MIN(rowid), col1, col2
FROM t
GROUP BY col1, col2;

DELETE FROM t
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM t
GROUP BY col1, col2);

COMMIT;

SELECT * FROM t;