| Basic Group By |
|
GROUP BY is used in conjunction with aggregating functions to group the results by the unaggregated columns |
| Aggregating Functions |
AVG
COUNT
FIRST
LAST
MAX
MIN
STDDEV
SUM
VARIANCE
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;
|
