Home » Developer & Programmer » Reports & Discoverer » count stuid
count stuid [message #669220] Mon, 09 April 2018 08:11 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL>desc student
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 STUID                                     NOT NULL NUMBER(8)
 STATUS                                             VARCHAR2(30)
 CLASS                                              VARCHAR2(25)
 SECTION                                            VARCHAR2(30)
 NAME                                               VARCHAR2(250)
 F_NAME                                             VARCHAR2(250)
 DOA                                                DATE
 DOB                                                DATE

i want to count no of student on specific date.My total strength on '26-DEC-2017' is 1234. and the system showing 1084.i am using this query.
SQL> select count(stuid) from student
  2  where status='PRESENT'
  3  AND DOA <'26-DEC-2017';
COUNT(STUID)
------------
         1084

Re: count stuid [message #669221 is a reply to message #669220] Mon, 09 April 2018 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>My total strength on '26-DEC-2017' is 1234.
post SQL & results that show above is correct.
Re: count stuid [message #669222 is a reply to message #669220] Mon, 09 April 2018 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
How does DOA < date equate to "count no of student on specific date"?

Seems like they are different things, but I'm just guessing since I have no idea what DOA is
Re: count stuid [message #669224 is a reply to message #669222] Mon, 09 April 2018 08:37 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
DOA means is date of admission
Re: count stuid [message #669226 is a reply to message #669224] Mon, 09 April 2018 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below

select count(stuid) from student DOA <'26-DEC-2017';
Re: count stuid [message #669227 is a reply to message #669226] Mon, 09 April 2018 09:03 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL> select count(stuid) from student where DOA <'26-DEC-2017';

COUNT(STUID)
------------
1108
Re: count stuid [message #669229 is a reply to message #669227] Mon, 09 April 2018 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>My total strength on '26-DEC-2017' is 1234.
post SQL & results that show above is correct.
Re: count stuid [message #669230 is a reply to message #669224] Mon, 09 April 2018 09:15 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
How does status figure into it?
If that's the status of the student now, it doesn't mean it was the status of the student on the date in question (or does it?)
Re: count stuid [message #669231 is a reply to message #669230] Mon, 09 April 2018 09:17 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
And as BS keeps asking - how did you determine 1234 is the right answer? The fact it's that number makes me suspicious - it's a number people like to type. It's not a number people like to set up that many records to match.
Re: count stuid [message #669232 is a reply to message #669227] Mon, 09 April 2018 09:33 Go to previous messageGo to next message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that your date string is being interpreted as you want? If you don't use proper type casting, you can get some pretty odd results. For example, in the SH schema, what was sold on new year's eve:
orclx>
orclx> conn sh/sh
Connected.
orclx> select sum(quantity_sold) from sales where trunc(time_id)='2001-12-31';

SUM(QUANTITY_SOLD)
------------------
               786

1 row selected.

orclx> select sum(quantity_sold) from sales where trunc(time_id)='31-DEC-2001';

SUM(QUANTITY_SOLD)
------------------


1 row selected.

orclx>
Which of those is correct?
Re: count stuid [message #669234 is a reply to message #669231] Mon, 09 April 2018 09:40 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL> select count(stuid) from student;

COUNT(STUID)
------------
         1234
this is the last print backup i have.
Re: count stuid [message #669235 is a reply to message #669232] Mon, 09 April 2018 09:40 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the first one works, shouldn't the 2nd throw a date conversion error?
Re: count stuid [message #669236 is a reply to message #669234] Mon, 09 April 2018 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any why would total rows in the table be the same as "no of student on specific date"?
Re: count stuid [message #669238 is a reply to message #669235] Mon, 09 April 2018 09:44 Go to previous messageGo to next message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
Sometimes I think that Oracle is too clever for its own good. Here is what is happening:
orclx>
orclx> host echo %nls_date_format%
yyyy-mm-dd:hh24:mi:ss

orclx> select sysdate from dual;

SYSDATE
-------------------
2018-04-09:15:44:19

1 row selected.

orclx> select to_Date('31-DEC-2001') from dual;

TO_DATE('31-DEC-200
-------------------
0031-12-20:01:00:00

1 row selected.

orclx>
Re: count stuid [message #669239 is a reply to message #669236] Mon, 09 April 2018 09:47 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SELECT distinct COUNT(STUDENT.STUID) str, 
STUDENT.CLASS||' '||STUDENT.SECTION sec, STUDENT.STUDENTID
FROM STUDENT
WHERE STATUS='PRESENT'
and doa < :p_1
GROUP BY STUDENT.CLASS||' '||STUDENT.SECTION, 
STUDENT.STUDENTID
report main query
Re: count stuid [message #669243 is a reply to message #669239] Mon, 09 April 2018 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what does that prove?
Why do you think the answer should be 1234?
Why do you think the results you got above are actually wrong?
Re: count stuid [message #675537 is a reply to message #669243] Thu, 04 April 2019 04:04 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member

CREATE TABLE STUDent ( 
stuid number(6) primary key,status varchar2(20),doa date,leav_date date);

insert into STUDent (stuid,status,doa,leav_date) 
values 
(1,'PRESENT','03-APR-2017','03-APR-2019');
insert into STUDent (stuid,status,doa,leav_date)
values 
(2,'N_PRESENT','23-APR-2017','03-MAY-2019');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(3,'PRESENT','03-APR-2017','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(4,'PRESENT','23-APR-2017','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(5,'PRESENT','13-APR-2017','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(6,'N_PRESENT','15-MAY-2017','03-APR-2019');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(7,'N_PRESENT','03-APR-2017','03-APR-2019');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(8,'N_PRESENT','23-MAR-2017','03-MAY-2019');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(9,'PRESENT','03-MAR-2016','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(10,'PRESENT','23-OCT-2018','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(11,'PRESENT','13-SEP-2017','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(12,'PRESENT','15-JUL-2018','');
insert into STUDent (stuid,status,doa,leav_date) 
values 
(13,'PRESENT','03-APR-2018','');


Quote:

SELECT distinct COUNT(STUDENT.STUID) str FROM STUDENT
WHERE STATUS='PRESENT'
and doa < :p_1
But cannot get the required result.

Quote:

Result Required:
Date nO OF sTUDENT
12-JAN-2019 5
13-JAN-2019 7
14-JAN-2019 6
22-JAN-2019 7
23-JAN-2019 7
28-JAN-2019 8

ON SPECIFIC DATE whether the student is Present or N_Present. shows the equal strength. if a student is present on a date(12-jan-2019) and leave the school after the enter date on (13-Jan-2019). when i check (13-jan-2019) it will not show that student. when i check (12-jan-2019) it will show that student.

[Updated on: Thu, 04 April 2019 04:12]

Report message to a moderator

Re: count stuid [message #675541 is a reply to message #675537] Thu, 04 April 2019 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Use [code] tags for code not [quote] tags
2) That select very obviously can not give the results below it since the query selects one column and the output has two.
3) What determines which dates should be in the output?
Re: count stuid [message #675543 is a reply to message #675541] Thu, 04 April 2019 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

4) Feedback in your previous topics and post the solution you found.
5) Post a VALID test case

SQL> select to_date('03-APR-2017') from dual;
select to_date('03-APR-2017') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Re: count stuid [message #675583 is a reply to message #675541] Mon, 08 April 2019 01:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SELECT distinct COUNT(STUDENT.STUID) str FROM STUDENT
WHERE STATUS='PRESENT'
and doa < :p_1

the one column is count stuid and other is in where condition.


Quote:

https://livesql.oracle.com/apex/f?p=590:24:100875633833977:::24:P24_SESSION_ID:177699316966249721265376092760864101546&cs=3qZ53vN 8-hoQLI71kxcQZ2QrIS3JU_LzelDCGF266SHFHuXmblGh2vItgUFHgNEbiSFFmVDVRXXIXuMPobHg88w

[Updated on: Mon, 08 April 2019 01:14]

Report message to a moderator

Re: count stuid [message #675588 is a reply to message #675583] Mon, 08 April 2019 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which very obviously means it can't possibly give the results you say you want, since those results display two columns.
So do you actually want two columns in the output or not?
If you do then you need to select two columns.
Re: count stuid [message #677623 is a reply to message #675588] Tue, 01 October 2019 08:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Here is answer FOR Michel Cadot
SQL> SELECT distinct COUNT(STUDENT.STUID) str,
  2  STUDENT.CLASS||' '||STUDENT.SECTION sec, STUDENT.STUDENTID
  3  FROM STUDENT
  4  WHERE STATUS='PRESENT'
  5  and doa < '08-FEB-2019'
  6  GROUP BY STUDENT.CLASS||' '||STUDENT.SECTION,
  7  STUDENT.STUDENTID;
Re: count stuid [message #677634 is a reply to message #677623] Tue, 01 October 2019 10:42 Go to previous message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 04 April 2019 12:40

...
SQL> select to_date('03-APR-2017') from dual;
select to_date('03-APR-2017') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
You did learn nothing.


Previous Topic: frm-41214: Unable to run report
Next Topic: multiples rows into one row.
Goto Forum:
  


Current Time: Sun Oct 20 16:25:02 CDT 2019