## MODEL Clause

### Aggregate and Analytic Functions

Queries with a MODEL clause cannot specify aggregate or analytic functions in their SELECT or ORDER BY lists. For example, this attempt generates an error.

```select
group_1 ,
MIN( GROUP_2 ) as c_1 ,
MAX( GROUP_2 ) as c_2 ,
SUM( NUM_VAL ) as c_3
from
t
group by
group_1
model
dimension by( group_1 )
measures    ( group_2, num_val )
rules ( )
order by
group_1
;
measures    ( group_2, num_val )
*
ERROR at line 12:
ORA-00979: not a GROUP BY expression

```

If needed, these functions can instead be specified in the PARTITION BY, DIMENSION BY, MEASURES, or RULES clauses. The following examples demonstrate their use in DIMENSION BY and MEASURES.

##### Aggregate Example
```column d_1 format a10
column d_2 format a10
column m_1 format 999999

select
group_1, d_1, d_2, m_1
from
t
group by
group_1
model
dimension by
(
group_1 ,
MIN( GROUP_2 ) as d_1  ,
MAX( GROUP_2 ) as d_2
)
measures
(
SUM( NUM_VAL ) as m_1
)
rules ( )
order by
group_1
;
```
```GROUP_1    D_1        D_2            M_1
---------- ---------- ---------- -------
A          a1         a3             600
B          a1         a3             400
C          a1         a2             100
a1         a2            1000
```
##### Analytic Example
```break on group_1 skip 1 duplicates

column d_1 format a10
column d_2 format a10
column m_1 format 999999

select
group_1, group_2, d_1, d_2, num_val, m_1
from
t
model
dimension by
(
group_1 ,
group_2 ,
MIN( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_1 ,
MAX( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_2
)
measures
( num_val ,
SUM( NUM_VAL ) OVER ( PARTITION BY GROUP_1 ) as m_1
)
rules ( )
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2    D_1        D_2        NUM_VAL     M_1
---------- ---------- ---------- ---------- ------- -------
A          a1         a1         a3             100     600
A          a2         a1         a3             200     600
A          a3         a1         a3             300     600

B          a1         a1         a3                     400
B          a2         a1         a3             300     400
B          a3         a1         a3             100     400

C          a1         a1         a2             100     100
C          a2         a1         a2                     100

a1         a1         a2             200    1000
a2         a1         a2             800    1000
```

#### RULES

You can also use aggregate and analytic functions in the RULES clause, but it is not as straightforward as using them in DIMENSION BY and MEASURES is. See the tutorial Aggregate and Analytic Expressions for more information on this topic.

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11791.html]SQL Snippets: MODEL Clause - Aggregate and Analytic Functions[/url]