Home » SQL & PL/SQL » SQL & PL/SQL » Page Pagination with a Count (can this be done)
Page Pagination with a Count (can this be done) [message #280702] Wed, 14 November 2007 09:56 Go to next message
Duane
Messages: 452
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 #280715 is a reply to message #280702] Wed, 14 November 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is student in all this stuff?

Regards
Michel
Re: Page Pagination with a Count (can this be done) [message #280725 is a reply to message #280702] Wed, 14 November 2007 10:38 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
A student is identified by their emplid, so, a student is an emplid. Did that answer your question?
Re: Page Pagination with a Count (can this be done) [message #280728 is a reply to message #280725] Wed, 14 November 2007 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The number of students is then count(emplid) from ps_names?

Regards
Michel
Re: Page Pagination with a Count (can this be done) [message #280734 is a reply to message #280702] Wed, 14 November 2007 11:01 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
That would give me duplicates, right?
Re: Page Pagination with a Count (can this be done) [message #280736 is a reply to message #280734] Wed, 14 November 2007 11:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Perhaps COUNT(distinct emplid) ?
Re: Page Pagination with a Count (can this be done) [message #280740 is a reply to message #280702] Wed, 14 November 2007 11:15 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
That just gives me a group by error when I put the count after the "max_effdt" statement.
Re: Page Pagination with a Count (can this be done) [message #280743 is a reply to message #280734] Wed, 14 November 2007 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows? I don't what is table ps_names, it is just a question.
Are you aware that only you knows the model and the data?
Post a description of each table and what's inside and maybe we can answer your question.

Regards
Michel
Re: Page Pagination with a Count (can this be done) [message #280744 is a reply to message #280702] Wed, 14 November 2007 11:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Duane
Messages: 452
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 #280988 is a reply to message #280800] Thu, 15 November 2007 05:48 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
However beware of this:
Where bad performance starts at http://gplivna.blogspot.com/2007/03/where-bad-performance-starts-my.html

Gints Plivna

[Updated on: Fri, 07 December 2007 13:41] by Moderator

Report message to a moderator

Re: Page Pagination with a Count (can this be done) [message #281044 is a reply to message #280702] Thu, 15 November 2007 08:36 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
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.
Re: Page Pagination with a Count (can this be done) [message #281123 is a reply to message #281044] Thu, 15 November 2007 16:14 Go to previous message
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 Wink

Gints Plivna

[Updated on: Fri, 07 December 2007 13:41] by Moderator

Report message to a moderator

Previous Topic: Average every 7 days
Next Topic: How to trim trailing spaces in DBMS_OUTPUT
Goto Forum:
  


Current Time: Wed Dec 07 20:26:17 CST 2016

Total time taken to generate the page: 0.19980 seconds