CODEWITHSUNDEEP
sqloracle-database
Marvin Wong
Marvin Wong

Reputation: 519

SQL: Create Trailer Count

I have created a query that will output a flat file with header and details. Now, I want to add a trailer record that will contain the total count of the detail records.

I have correctly counted the total of records using row_number, but it displays the every record.

How can I get the last line so that it will reflect the total count in the trailer line.

This is the code I already created for the headers and detail.

SQL> SELECT filerec FROM (
  2      SELECT 'FILENAME' AS filerec, 1 col FROM dual
  3      UNION ALL
  4      SELECT 'FILEDATE: ' || to_char(SYSDATE,'mm/dd/yyyy') as filerec, 2 col FROM dual
  5      UNION ALL
  6      SELECT empno || ename AS filerec, NULL col FROM emp
  7      ORDER BY 2,1
  8  );

This is the output I want to get. (added the last rec, 'TRAILER: 0004')

FILENAME
FILEDATE: 02/27/2015
7369SMITH
7499ALLEN
7521WARD
7566JONES
TRAILER: 0004

Upvotes: 0

Views: 1470

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

There are several ways to do this. I'd prefer to use grouping. See how it can be used. Let we have a recordset:

SQL> select
  2    'Row ' || rownum
  3  from
  4    dual
  5  connect by
  6    level <= 5;

'ROW'||ROWNUM
--------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5

Now we wish to add counting:

SQL> select
  2    case
  3      when grouping_id(rownum) = 0 then 'Row ' || rownum
  4      else 'Total: ' || count(*) || ' row(s)'
  5    end
  6  from
  7    dual
  8  connect by
  9    level <= 5
 10  group by rollup (rownum);

CASEWHENGROUPING_ID(ROWNUM)=0T
------------------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5
Total: 5 row(s)

6 rows selected

Upvotes: 0

Related Questions