CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle GROUP BY & HAVING Clauses
Version 11.1
 
Basic Group By
GROUP BY is used in conjunction with aggregating functions to group the results by the unaggregated columns
Aggregating Functions AVG: returns the variance or variability of an expression.
COUNT: returns the number of rows returned by a query.
FIRST: returns the first value from an ordered sequence.
LAST: returns the last value from an ordered sequence.
MAX: returns the maximum value of a column.
MIN: returns the minimum value of a column.
STDDEV: returns the standard deviation of a set of numbers.
SUM: returns the sum or total the values of a column.
VARIANCE: returns the variance or variability of an expression.

For more use the link to Aggregating Functions at the bottom of the page
 
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
CREATE TABLE grp_test (
year   VARCHAR2(4),
result NUMBER(7));

INSERT INTO grp_test VALUES ('2002', 1000);
INSERT INTO grp_test VALUES ('1998', 1250);
INSERT INTO grp_test VALUES ('1999', 3786);
INSERT INTO grp_test VALUES ('2000', 977);
INSERT INTO grp_test VALUES ('1997', 5000);
INSERT INTO grp_test VALUES ('2001', 123);
INSERT INTO grp_test VALUES ('1998', 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-1999),1,year,'1999 or before'), SUM(result)
FROM grp_test
GROUP BY DECODE(SIGN(year-1999),1,year,'1999 or before');
 
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
-- To create the all_objs table: Click Here

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

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;
 
Removing Duplicate Rows With GROUP BY

Duplicate record Removal Demo
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
Aggregating Functions
CUBE
DECODE
GROUP_ID
GROUPING_ID
GROUPING
GROUPING_SETS
ROLLUP
SELECT
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [142 users online]    © 2010 psoug.org