derived-table expressions can use aggregate functions, such as sum, avg, max, min, count_big, and count. The following example selects columns pub_id and adv_sum from the SQL-derived table dt_a. The second column is created in the derived-table expression using the sum function over the advance column of the titles table.
select dt_a.pub_id, dt_a.adv_sum from (select pub_id, sum(advance) adv_sum from titles group by pub_id) dt_a
If you use an aggregate function within a create view statement, you must specify a column name for the aggregate results.
