How to number lines, with total at end?

Question

My current sql:

select s.dcid, substr(s.lastfirst,0,3),  to_char(a.att_date, 'mm/dd/yyyy'), a.periodid, p.name,     a.attendance_codeid, ac.att_code, count(*)

from students s
join attendance a on s.id = a.studentid
join period p on a.periodid = p.id
join attendance_code ac on a.attendance_codeid = ac.id

WHERE ac.att_code IS NOT NULL 
AND s.schoolid = 109
AND s.enroll_status = 0
AND s.student_number = 100887
AND a.att_date >= to_date('08/15/2013', 'mm/dd/yyyy')


group by s.dcid, s.lastfirst,  to_char(a.att_date, 'mm/dd/yyyy'), a.periodid, p.name, a.attendance_codeid, ac.att_code

Output:

I would like to get the output to sequentially number each record where the count(*) column is, starting with 1 at each new group, and put a total at the bottom of the group, but I'm not sure how to do that. I have tried rollup at various parts of the group by expression, but it winds up giving subtotals for the dates, periodids, etc... I need it to total ONLY for the student (either s.dcid or s.lastfirst)

[Additional information per request...]

I'm hoping to achieve a report where my end users can search for students who have a given number of attendance records in a date range. For example, if the end user wants to find students who have 20 absences between 10/1/2013 and 10/31/2013, where the att_code is one of A,C,E,G... etc. Once the report runs, I want to show them the date the absence occurred, and the code that was used as a visual verification that the records found do indeed match their search criteria.

The output should look like the current output with the exception of the COUNT(*) column, which is where I'm hung up right now. I like how row_number sequentially numbers each record, but what I'm still seeking is how to reset the sequential numbering when the group (the student) changes.

For example...

DCID  S.LASTFIRST  A.ATT_DATE  PERIODID  NAME  ATT_CODE  COUNT(or # or Num...)
1006  Aco          08/29/2013      1704  4     W             1
1006  Aco          09/03/2013      1701  1     6             2
1006  Aco          09/05/2013      1706  6     G             3
...
1006  Aco          10/04/2013      1706  6     z            20
2543  Bro          08/29/2013      1704  4     W             1
2543  Bro          09/03/2013      1701  1     6             2
2543  Bro          09/05/2013      1706  6     G             3
...
2543  Bro          10/04/2013      1706  6     z            20
3121  Com          08/29/2013      1704  4     W             1
3121  Com          09/03/2013      1701  1     6             2
3121  Com          09/05/2013      1706  6     G             3
...
3121  Com          10/04/2013      1706  6     z            20

Of course, in this example, I am abbreviating the output by replacing row numbers 4 - 19 in each of the three groups with '...' I don't want to literally output this.

Answer 1

The ROW_NUMBER() analytical function will, unsurprisingly, number rows sequentially using the partitions and ordering you specify.

select s.dcid,
       substr(s.lastfirst,0,3),
       to_char(a.att_date, 'mm/dd/yyyy'),
       a.periodid,
       p.name,
       a.attendance_codeid,
       ac.att_code,
       ROW_NUMBER() OVER ( ORDER BY s.dcid )
from students s
join attendance a on s.id = a.studentid
join period p on a.periodid = p.id
join attendance_code ac on a.attendance_codeid = ac.id

WHERE ac.att_code IS NOT NULL 
AND s.schoolid = 109
AND s.enroll_status = 0
AND s.student_number = 100887
AND a.att_date >= to_date('08/15/2013', 'mm/dd/yyyy')
GROUP BY s.dcid,
         s.lastfirst,
         to_char(a.att_date, 'mm/dd/yyyy'),
         a.periodid,
         p.name,
         a.attendance_codeid,
         ac.att_code;

From your screenshot the COUNT() column is always 1 so the ROW_NUMBER() will also always be one (as that appears to be the maximum size of each group).

If this is not meant to be the case then you will need to be less restrictive in your GROUP BY clause - however you have not given enough information on what you expect the query to do for me to make any changes.

Answer 2

Use ROW_NUMBER function as follows:

SELECT s.dcid,
       SUBSTR (s.lastfirst, 0, 3),
       TO_CHAR (a.att_date, 'mm/dd/yyyy'),
       a.periodid,
       p.name,
       a.attendance_codeid,
       ac.att_code,
       ROW_NUMBER() OVER (ORDER BY s.dcid) AS rownumber
       -- I have ordered by s.dcid. You can order by whichever column you want.
  FROM students s
       JOIN attendance a ON s.id = a.studentid
       JOIN period p ON a.periodid = p.id
       JOIN attendance_code ac ON a.attendance_codeid = ac.id
 WHERE     ac.att_code IS NOT NULL
       AND s.schoolid = 109
       AND s.enroll_status = 0
       AND s.student_number = 100887
       AND a.att_date >= TO_DATE ('08/15/2013', 'mm/dd/yyyy');