Home » Developer & Programmer » Reports & Discoverer » student in specific year (orale 10g, Reports 6i)
student in specific year [message #683714] Tue, 16 February 2021 01:13 Go to next message
shahzad-ul-hasan
Messages: 580
Registered: August 2002
Senior Member
Database: 10G
Reports Version: 6i
create table student (
stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);
insert into student1 (stuid,status,name,class,doa) 
values 
(1,'PRESENT','AHA','PREP','01-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(4,'PRESENT','A','ONE','01-FEB-2016');
insert into student1 (stuid,status,name,class,doa) 
values 
(3,'PRESENT','AQ','ONE','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(2,'PRESENT','AER','PREP','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(29,'PRESENT','AERRRRRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(28,'PRESENT','AQRRRRH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(27,'PRESENT','AARAG','P.G','09-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(26,'PRESENT','AEARRTAA','P.G','11-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(30,'PRESENT','AER44RRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(31,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(32,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(33,'N_PRESENT','AEAAA','P.G','16-FEB-2016','19-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAVE_DATE)
values
(40,'N_PRESENT','AEARRTAA','P.G','11-FEB-2016','20-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(36,'N_PRESENT','AER44RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(37,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(38,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(39,'PRESENT','AEAAA','P.G','16-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(41,'PRESENT','AERRERRRRJ','P.G','20-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(42,'PRESENT','AQRWERWERRRRH','P.G','20-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(43,'PRESENT','AARAEEWG','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(44,'PRESENT','AEAWERRTAA','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(45,'PRESENT','AEREWEW44RRJ','P.G','13-MAR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(46,'PRESENT','AQEWTYTRYR3RH','P.G','01-APR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(47,'PRESENT','AATYRTYAG','P.G','19-FEB-2017');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(48,'N_PRESENT','AERWERAAA','P.G','16-FEB-2017','10-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(49,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(17,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(19,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(16,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
COMMIT;
I want to count the "PRESENT" student in specific year like how many "PRESENT" students in 2016 and 2017.
Re: student in specific year [message #683716 is a reply to message #683714] Tue, 16 February 2021 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67841
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a VALID test case.

Quote:
create table student
insert into student1
In addition, '23-JUN-2016' is a string NOT a date:
SQL> select to_date('23-JUN-2016') from dual;
select to_date('23-JUN-2016') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ALWAYS use TO_DATE and specify the format you use.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.

In the end, you just have to count grouping by year:
SQL> select extract(year from hiredate) year, count(decode(deptno,10,empno)) nb_in_dept10
  2  from emp
  3  group by extract(year from hiredate)
  4  /
      YEAR NB_IN_DEPT10
---------- ------------
      1982            1
      1987            0
      1980            0
      1981            2
or
SQL> select extract(year from hiredate) year, count(*)  nb_in_dept10
  2  from emp
  3  where deptno = 10
  4  group by extract(year from hiredate)
  5  /
      YEAR NB_IN_DEPT10
---------- ------------
      1982            1
      1981            2
depending if you want all years in the table or only those with some rows.

[Updated on: Tue, 16 February 2021 06:53]

Report message to a moderator

Re: student in specific year [message #683733 is a reply to message #683716] Wed, 17 February 2021 00:11 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 580
Registered: August 2002
Senior Member
sorry for writing problem
create table student1 (
stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);
insert into student1 (stuid,status,name,class,doa) 
values 
(1,'PRESENT','AHA','PREP','01-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(4,'PRESENT','A','ONE','01-FEB-2016');
insert into student1 (stuid,status,name,class,doa) 
values 
(3,'PRESENT','AQ','ONE','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa) 
values 
(2,'PRESENT','AER','PREP','11-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(29,'PRESENT','AERRRRRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(28,'PRESENT','AQRRRRH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(27,'PRESENT','AARAG','P.G','09-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(26,'PRESENT','AEARRTAA','P.G','11-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(30,'PRESENT','AER44RRJ','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(31,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(32,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(33,'N_PRESENT','AEAAA','P.G','16-FEB-2016','19-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAVE_DATE)
values
(40,'N_PRESENT','AEARRTAA','P.G','11-FEB-2016','20-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(36,'N_PRESENT','AER44RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(37,'PRESENT','AQR3RH','P.G','13-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(38,'PRESENT','AAAG','P.G','19-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(39,'PRESENT','AEAAA','P.G','16-FEB-2016');
insert into student1 (stuid,status,name,class,doa)
values
(41,'PRESENT','AERRERRRRJ','P.G','20-JAN-2016');
insert into student1 (stuid,status,name,class,doa)
values
(42,'PRESENT','AQRWERWERRRRH','P.G','20-JAN-2017');
insert into student1 (stuid,status,name,class,doa)
values
(43,'PRESENT','AARAEEWG','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(44,'PRESENT','AEAWERRTAA','P.G','13-FEB-2017');
insert into student1 (stuid,status,name,class,doa)
values
(45,'PRESENT','AEREWEW44RRJ','P.G','13-MAR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(46,'PRESENT','AQEWTYTRYR3RH','P.G','01-APR-2017');
insert into student1 (stuid,status,name,class,doa)
values
(47,'PRESENT','AATYRTYAG','P.G','19-FEB-2017');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(48,'N_PRESENT','AERWERAAA','P.G','16-FEB-2017','10-SEP-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(49,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(17,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(19,'N_PRESENT','AEARTYTRYRRTAA','P.G','11-FEB-2017','10-OCT-2016');
insert into student1 (stuid,status,name,class,doa,LEAV_DATE)
values
(16,'N_PRESENT','AER4TRYR4RRJ','P.G','13-JAN-2016','23-JUN-2016');
COMMIT;
Requried result:
Quote:

required result
no of PRESENT Student doa < '01-JAN-2017'
----------------------
34

no of PRESENT Student doa < '01-JAN-2015'
----------------------
24

Re: student in specific year [message #683734 is a reply to message #683716] Wed, 17 February 2021 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67841
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 February 2021 08:35

...
Quote:
create table student
insert into student1
In addition, '23-JUN-2016' is a string NOT a date:
SQL> select to_date('23-JUN-2016') from dual;
select to_date('23-JUN-2016') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ALWAYS use TO_DATE and specify the format you use.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.

...
SQL> create table student1 (
  2  stuid number(7) primary key, status varchar2(30),name varchar2(100),class varchar2(40),doa date);

Table created.

SQL> insert into student1 (stuid,status,name,class,doa)
  2  values
  3  (1,'PRESENT','AHA','PREP','01-JAN-2017');
(1,'PRESENT','AHA','PREP','01-JAN-2017')
                          *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: student in specific year [message #683752 is a reply to message #683734] Wed, 17 February 2021 21:22 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 580
Registered: August 2002
Senior Member
please find the attach file as test case.My NLS_Date format is : 'DD-MON-YY'

[Updated on: Wed, 17 February 2021 21:23]

Report message to a moderator

Re: student in specific year [message #683753 is a reply to message #683752] Thu, 18 February 2021 00:15 Go to previous message
Michel Cadot
Messages: 67841
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I see your test case in your previous topic, no need to post it again as a file.
I don't care about your date format, I won't change my settings for you.
You MUST update your test case to make it work for ANYONE.
So I repeat one more time:

Michel Cadot wrote on Wed, 17 February 2021 07:28
Michel Cadot wrote on Tue, 16 February 2021 08:35

...
ALWAYS use TO_DATE and specify the format you use in this function.
If you use month or day names, specify, in the TO_DATE function, the language you use; or better use month number.

...
...

This is basic SQL but as I am in a good mood I give the link you have to read:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm#i1003589

Previous Topic: Arabic Report In Adobe PDF printer
Next Topic: how to create chart in oracle reports 6i
Goto Forum:
  


Current Time: Sat May 08 08:50:02 CDT 2021