Page Pagination with a Count (can this be done) [message #280702] |
Wed, 14 November 2007 09:56  |
Duane
Messages: 585 Registered: December 2002
|
Senior Member |
|
|
I'm trying to obtain a total count with a page pagination query. What I'm after is the total number of students not just the total number of rows returned.
select *
from (select a.*,
rownum r
from (select a.emplid,
class_nbr,
first_name,
last_name
from (select emplid,
class_nbr
from ps_stdnt_enrl
where strm = '3543' and
class_nbr in ('43658', '43659')) a,
(select emplid,
effdt,
eff_status,
first_name,
last_name,
name_type,
max(effdt) over (partition by emplid, name_type) max_effdt
from ps_names) b
where a.emplid = b.emplid and
b.eff_status = 'A' and
b.name_type = 'PRI'
b.effdt = b.max_effdt) a
where rownum <= 5)
where r >= 1
So, if this query found 25 students in the two classes, I want to display a count of 25 in a column to reference. Now, remember, I don't need a total count of rows since a student can be in both classes. I just need a total count of students that's independent of the number of classes they are taking.
Why you ask? Because I want to display the total number of students the query returned without running a separate query to find the count. I'm thinking it can't be done without doing a second query but maybe there is a way to do it.
|
|
|
|
|
|
|
|
|
|
Re: Page Pagination with a Count (can this be done) [message #280744 is a reply to message #280702] |
Wed, 14 November 2007 11:35   |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
I'm not completely sure I understood your requirements corectly, but let's try:
I'll create my own example with simple students table and add more than one student with the same i to simulate your situation:
SQL> create table students (
2 std_id number,
3 name varchar2(10));
Table created.
SQL> insert into students select rownum, substr(object_name, 1, 10)
2 from all_objects where rownum <= 25;
25 rows created.
SQL> insert into students select * from students
2 where mod(trunc(std_id/2), 2) = 0;
13 rows created.
SQL> commit;
Using http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:127412348064 let's creat simple query:
SQL> ed
Wrote file afiedt.buf
1 select *
2 from ( select a.*, rownum rnum
3 from (select * from students order by name) a
4 where rownum <= 25 )
5* where rnum >= 15
SQL> /
STD_ID NAME RNUM
---------- ---------- ----------
19 I_CDEF4 15
16 I_CON1 16
16 I_CON1 17
14 I_FILE#_BL 18
15 I_FILE1 19
23 I_FILE2 20
8 I_IND1 21
8 I_IND1 22
6 I_OBJ# 23
17 I_OBJ3 24
17 I_OBJ3 25
11 rows selected.
And now counting just distinct student ids:
SQL> ed
Wrote file afiedt.buf
1 select b.*, count(distinct std_id) over ()
2 from ( select a.*, rownum rnum
3 from (select * from students order by name) a
4 where rownum <= 25 ) b
5* where rnum >= 15
SQL> /
STD_ID NAME RNUM COUNT(DISTINCTSTD_ID)OVER()
---------- ---------- ---------- ---------------------------
6 I_OBJ# 23 8
8 I_IND1 22 8
8 I_IND1 21 8
14 I_FILE#_BL 18 8
15 I_FILE1 19 8
16 I_CON1 17 8
16 I_CON1 16 8
17 I_OBJ3 25 8
17 I_OBJ3 24 8
19 I_CDEF4 15 8
23 I_FILE2 20 8
11 rows selected.
Gints Plivna
[Updated on: Fri, 07 December 2007 13:42] by Moderator Report message to a moderator
|
|
|
Re: Page Pagination with a Count (can this be done) [message #280800 is a reply to message #280702] |
Wed, 14 November 2007 14:54   |
Duane
Messages: 585 Registered: December 2002
|
Senior Member |
|
|
That's it. I wanted to know the count in the second query, which is, 8 students. Even though there are 11 rows there are only 8 students.
The requirements are list out the students for a set of classes but give the count of the students. Since students can take multiple classes, you can't just count the number of the rows (11 in your example) that the query returns.
Even though I'm only displaying 1-3 in my example (lowerbound, upperbound) I still want to know how many students the whole query returned.
Something like this:
Displaying 1-3 of 20
John Doe Class 1
Class 2
Class 3
Jane Doe Class 3
Superman Class 2
Class 3
I ended up doing the "count" part with the same query but in a different block. I just wanted to do everything in one query and not have to run two queries.
I tried the count() over but never could get the correct result. I see that I must imbed the whole query in the count statement to achieve the result I want.
|
|
|
|
|
Re: Page Pagination with a Count (can this be done) [message #281123 is a reply to message #281044] |
Thu, 15 November 2007 16:14  |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
Duane wrote on Thu, 15 November 2007 16:36 | That was a good read, unfortunately, when administration needs the actual student count versus some arbitrary close count, I must comply and print the actual count of students that meet that criteria.
|
Oh yea, no problem, just understand that this is report and reports usually are at least additional subsystem for an OLTP application.
Don't know how many data/users you have, probably it is not important to you, but the concept - one should get rid of bad requirements as much as possible - is very important 
Gints Plivna
[Updated on: Fri, 07 December 2007 13:41] by Moderator Report message to a moderator
|
|
|