MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Collection Aggregation

with 2 comments

The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.

The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.

1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Conditionally delete objects in reverse dependency order.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_type IN ('TABLE','SEQUENCE','TYPE')
            AND      object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE'
                                    ,'CONTACT_BOOK','CONTACT_BOOK_S1')
            ORDER BY object_type, object_name DESC) LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
END;
/
 
-- Create a composite data type (structure) and collection.
CREATE TYPE telephone_type AS OBJECT
( country_code   VARCHAR2(3)
, prefix_code    VARCHAR2(5)
, phone_number   VARCHAR2(9));
/
 
CREATE TYPE telephone_table AS TABLE OF telephone_type;
/
 
-- Create a table with a nested composite table and sequence.
CREATE TABLE contact_book
( contact_book_id   NUMBER
, first_name        VARCHAR2(30)
, last_name         VARCHAR2(30)
, telephone_numbers TELEPHONE_TABLE )
NESTED TABLE telephone_numbers STORE AS telephone_struct;
 
CREATE SEQUENCE contact_book_s1;
 
-- Insert two rows of data.
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Harry','Potter'
,telephone_table(telephone_type('44','20','7299 2127')
                ,telephone_type('44','17','8926 8115')
                ,telephone_type('44','18','3344 5566')
                ,telephone_type('44','72','9878 5436')));
 
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Weasley','Ronald'
,telephone_table(telephone_type('40','021','407 47 46')
                ,telephone_type('44','19','4088 1062')
                ,telephone_type('44','21','4075 5066')));

2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the CARDINALITY function introduced in Oracle 10g. The second leverages a CROSS JOIN and traditional aggregation tools.

2(a). Using the CARDINALITY function is generally the simplest and best approach to this problem:

1
2
3
4

SELECT first_name
,      last_name
,      CARDINALITY(telephone_numbers) list
FROM   contact_book;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Weasley      Ronald            3

2(b). Using the traditional CROSS JOIN and GROUP BY clause:

1
2
3
4
5
6
7

SELECT   cj.first_name
,        cj.last_name
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Ronald       Weasley           3

3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a CROSS JOIN and GROUP BY clause, like this:

1
2
3
4
5
6
7
8
9

SELECT   cj.first_name
,        cj.last_name
,        cj.country_code
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name
,        cj.country_code;

This yield the following data set:

FIRST_NAME   LAST_NAME    COUNTRY_CODE   LIST
------------ ------------ ------------ ------
Harry        Potter       44                4
Ronald       Weasley      44                2
Ronald       Weasley      40                1

If I captured the basics of the question, great. If there are more questions, please let me know.

Written by maclochlainn

September 2nd, 2009 at 12:06 am

Posted in Oracle,sql

«
»