Oracle GROUP BY & HAVING Clauses
Version 12.1.0.2

Basic Group By
GROUP BY is used in conjunction with aggregating functions to group the results by the unaggregated columns
Aggregating Functions
AVG RANK STATS_MW_TEST
CORR REGR_AVGX STATS_ONE_WAY_ANOVA
CORR_K REGR_AVGY STATS_T_TEST_INDEP
CORR_S REGR_COUNT STATS_T_TEST_INDEPU
COUNT REGR_INTERCEPT STATS_T_TEST_ONE
COVAR_POP REGR_R2 STATS_T_TEST_PAIRED
COVAR_SAMP REGR_SLOPE STATS_WSR_TEST
CUME_DIST REGR_SXX STDDEV
DENSE_RANK REGR_SXY STDDEV_POP
LISTAGG REGR_SYY STDDEV_SAMP
MAX STATS_BINOMIAL_TEST SUM
MEDIAN STATS_CROSSTAB SYS_OP_COUNTCHG
MIN STATS_F_TEST VARIANCE
PERCENTILE_CONT STATS_KS_TEST VAR_POP
PERCENTILE_DISC STATS_MODE VAR_SAMP
PERCENT_RANK    
 
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;

Related Topics
Built-in Functions
CUBE
DECODE
GROUP_ID
GROUPING_ID
GROUPING
GROUPING_SETS
ROLLUP
SELECT

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved