CODEWITHSUNDEEP
sqloracle11gmaxrownum
zundarz
zundarz

Reputation: 1594

How to include total rows returned as a column value?

Oracle 11g

I'd like to include max(rownum) as a new column to get the desired results. In essence, I'd like add the results from this QueryA as a new column to QueryB. When I include max(rownum) in QueryB I get a 'not a single-group function. Nor does QueryB work when I group by cola or rownum.

QueryA

with data_row as
(
select 1 as col_a from dual union all
select 2 as col_a from dual union all 
select 3 as col_a from dual )
select max(rownum) as max_row from data_row

QueryB

with data_row as
( select 1 as col_a from dual union all
  select 2 as col_a from dual union all
  select 3 as col_a from dual)
select col_a, rownum from data_row

Desired Result

Col_a    Rownum   MaxRowNum
--------------------------------
1          1       3
2          2       3
3          3       3

Upvotes: 1

Views: 356

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You can use the analytic form of the count function

SQL> ed
Wrote file afiedt.buf

  1  with data_row as (
  2    select 1 as colA from dual union all
  3    select 2 as cola from dual union all
  4    select 3 as cola from dual
  5  )
  6  select colA, rownum, count(*) over () cnt
  7*   from data_row
SQL> /

      COLA     ROWNUM        CNT
---------- ---------- ----------
         1          1          3
         2          2          3
         3          3          3

Upvotes: 2

Related Questions