| count stuid [message #669220] |
Mon, 09 April 2018 08:11  |
shahzad-ul-hasan
Messages: 643
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 #669222 is a reply to message #669220] |
Mon, 09 April 2018 08:32  |
cookiemonster
Messages: 13963
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 #669230 is a reply to message #669224] |
Mon, 09 April 2018 09:15 |
cookiemonster
Messages: 13963
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 |
cookiemonster
Messages: 13963
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 |
John Watson
Messages: 8964
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> |
|
|
|
|
|
|
|
|
|
| Re: count stuid [message #669238 is a reply to message #669235] |
Mon, 09 April 2018 09:44 |
John Watson
Messages: 8964
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 |
shahzad-ul-hasan
Messages: 643
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
|
|
|
|
| Re: count stuid [message #669243 is a reply to message #669239] |
Mon, 09 April 2018 10:14 |
cookiemonster
Messages: 13963
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 |
shahzad-ul-hasan
Messages: 643
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 |
cookiemonster
Messages: 13963
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 #675588 is a reply to message #675583] |
Mon, 08 April 2019 03:24 |
cookiemonster
Messages: 13963
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 |
shahzad-ul-hasan
Messages: 643
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
