Home » SQL & PL/SQL » SQL & PL/SQL » How to find total count based on date (Oracle 11gR2)
How to find total count based on date [message #624048] Wed, 17 September 2014 07:45 Go to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Hello Everyone,

I have come across a question where i need to find the total count of authors those have been entered into the authors table (as attached in the xls) till date.

As per my understanding, for today's date i will get the all total count of the records. For yesterday i will get the records count as total_count - the number of authors entered today and so on.

Could you please clarify whether my understanding is correct and also could you please guide me how to proceed.

The table data and the required output has been attached in the excel sheet.

Please note we dont have any ndexed column as such in the author table.

Regards,

Deb
Re: How to find total count based on date [message #624051 is a reply to message #624048] Wed, 17 September 2014 07:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: How to find total count based on date [message #624053 is a reply to message #624048] Wed, 17 September 2014 08:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Would I be correct in thinking that this is a college homework question?
Your description is hard to follow, but I think you need to look up the GROUP BY clause.

Incidentally, you will drop marks if you refer to "records" when you mean "rows". "Record" is meaningless to a relational engineer.
Re: How to find total count based on date [message #624054 is a reply to message #624051] Wed, 17 September 2014 08:04 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Hi,

Thanks for the reply. Please find the table structure and insert statement below.

CREATE TABLE authors
  (
     auth_email    VARCHAR2(100),
     lastlogindate DATE
  );


INSERT INTO authors
VALUES (abc@xyz.com,
'17-SEP-2014');

INSERT INTO authors
VALUES (def@xyz.com,
'17-SEP-2014');

INSERT INTO authors
VALUES (fgh@xyz.com,
'15-SEP-2014');

INSERT INTO authors
VALUES (xsa@xyz.com,
'14-SEP-2014');

INSERT INTO authors
VALUES (zxs@xyz.com,
'12-SEP-2014');

INSERT INTO authors
VALUES (def@xyz.com,
'11-SEP-2014');

INSERT INTO authors
VALUES (asx@xyz.com,
'10-SEP-2014');

INSERT INTO authors
VALUES (bgf@xyz.com,
'10-SEP-2014');

INSERT INTO authors
VALUES (nhg@xyz.com,
'08-SEP-2014');

COMMIT;

The requirement is to get the total count based on each date (but not the count of auth_id by date).

example- For 17-sep-2014 my count will give 9
For 16-sep-2014 my count will give 7
For 15-sep-2014 my count will give 7
For 14-sep-2014 my count will give 6.

I am new to this forum and I could not find how to show the required output in tabular format.

Please let me know if you need any further information from my end.

Many thanks for your help.

Regards,

Deb
Re: How to find total count based on date [message #624055 is a reply to message #624053] Wed, 17 September 2014 08:07 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Thanks for the reply. But as per my understanding group by will give me the count by date but not exactly the total count till that date.

regards,

Deb
Re: How to find total count based on date [message #624057 is a reply to message #624055] Wed, 17 September 2014 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't use GROUP BY (just COUNT) you have the total count.
And if you add a WHERE clause you can limit to day you want.

Re: How to find total count based on date [message #624060 is a reply to message #624057] Wed, 17 September 2014 08:26 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Hi,

Thanks for your reply.

I am not pretty sure how to show the desired output here in this forum. Any suggestion will help me a lot to show the desired output.

By using group by clause i will get the count by date. And by using where clause i can limit to a particular day.
But that is not the required output.

To elaborate the requirement in authors table auth_email are inserted on a daily basis. So if today 17-sep-2014 the total count is 9 and today 2 authors email id has been inserted then yesterday's count will be 7 and so on. By using group by clause i will get the count by date but not the total count till that date.

Please clarify me if my understanding is wrong.

regards,

Deb
Re: How to find total count based on date [message #624062 is a reply to message #624060] Wed, 17 September 2014 08:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Just use
where lastlogindate <= {input date}

and make sure you actually use a date as '17-SEP-2014' is a string, not a date.
Re: How to find total count based on date [message #624063 is a reply to message #624060] Wed, 17 September 2014 08:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
What do you want, clearly mention the rules. Do you want count per day or total count till date? Prior is count with group by, the latter is only count. As Michel already said, where clause will limit the number of days.
Re: How to find total count based on date [message #624066 is a reply to message #624055] Wed, 17 September 2014 08:47 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Your sample data does not contain date '16-sep-2014' found in your expected result.
Otherwise, there may be other solutions, but is this what you want?:
SQL> SELECT *
  2    FROM ( SELECT DISTINCT
  3                  Lastlogindate
  4                , COUNT ( * )
  5                    OVER ( ORDER BY Lastlogindate
  6                           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
  7                       Cnt
  8               FROM Authors )
  9     WHERE Lastlogindate >= TO_DATE ( '9/14/2014', 'MM/DD/YYYY' )
 10* ORDER BY 1 DESC
SQL> /

LASTLOGINDATE               CNT
-------------------- ----------
17-Sep-2014                   9
15-Sep-2014                   7
14-Sep-2014                   6

[Updated on: Wed, 17 September 2014 08:55] by Moderator

Report message to a moderator

Re: How to find total count based on date [message #624071 is a reply to message #624066] Wed, 17 September 2014 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is wrong.
If you use DISTINCT and analytic COUNT you are pretty sure the query is wrong.
What you want to do here is aggregation not reporting (the other name for analytic).

Re: How to find total count based on date [message #624076 is a reply to message #624066] Wed, 17 September 2014 09:28 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Thanks for the reply.

I have tried to make it clear. Please find the details below.

MY authors table is having below data

Table : Authors

AUTHOR_EMAIL LASTLOGINDATE
abc@xyz.com 17-SEP-2014'
def@xyz.com 17-SEP-2014'
fgh@xyz.com 15-SEP-2014'
xsa@xyz.com 14-SEP-2014'
zxs@xyz.com 12-SEP-2014'
def@xyz.com 11-SEP-2014'
asx@xyz.com 10-SEP-2014'
bgf@xyz.com 10-SEP-2014'
nhg@xyz.com 08-SEP-2014'


Now if i use below query my output is as shown below:

SELECT COUNT(auth_email), lastlogindate from authors group by lastlogindate order by lastlogindate desc


COUNT(AUTH_EMAIL) LASTLOGINDATE
2 17-SEP-2014
1 15-SEP-2014
1 14-SEP-2014
1 12-SEP-2014
1 11-SEP-2014
2 10-SEP-2014
1 08-SEP-2014

But this is what is not required.

If i execute individual queries as below:

select count(auth_email) from authors where lastlogindate <= sysdate; -- This is for 17-sep-2014



output
--------

COUNT(AUTH_EMAIL)
------------------
9

select count(auth_email) from authors  where lastlogindate <= sysdate -1; -- This is for 16-sep-2014



output
--------

COUNT(AUTH_EMAIL)
------------------
7

select count(auth_email)  from authors where lastlogindate <= sysdate -2; -- This is for 15-sep-2014


output
--------

COUNT(AUTH_EMAIL)
------------------
7

select count(auth_email)  from authors where lastlogindate <= sysdate -3; -- This is for 14-sep-2014


output
--------

COUNT(AUTH_EMAIL)
------------------
6

and so on...

I can get the total count till each day by executing individual queries as mentioned above.

My requirement is can i achieve the total count for each day till that day in a single query .

Below is the required output.

Count(AUTHOR_EMAIL) LASTLOGINDATE
9 17-SEP-2014
7 16-SEP-2014
7 15-SEP-2014
6 14-SEP-2014
5 13-SEP-2014
5 12-SEP-2014
4 11-SEP-2014
3 10-SEP-2014
1 09-SEP-2014
1 08-SEP-2014

Please note the count(AUTHOR_EMAIL) is the total count till the respective lastlogindate. That means till yesterday the total count was 7 as today two email id i.e abc@xyz.com and def@xyz.com logged in the total count showed is 9.

Please let me know if you need any further informations.

regards,

Deb
Re: How to find total count based on date [message #624077 is a reply to message #624066] Wed, 17 September 2014 09:31 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Thanks LKBrwn_DBA . This is true that I dont have any entry for lastlogindate as '16-SEP-2014'. But still it should have the same count as of '15-SEP-2014' as no authors has been logged in on 16-SEP-2014. So if i have to calculate the total author logged in on 16-SEP-2014 i believe i will get the same result as that of 15-SEP-2014.
Re: How to find total count based on date [message #624081 is a reply to message #624071] Wed, 17 September 2014 10:19 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Ok, ok ...
How about this?:
SQL> WITH Ldates ( Mindt, Maxdt )
  2  AS (SELECT MIN ( Lastlogindate ), MAX ( Lastlogindate ) FROM Authors)  
  3    SELECT Logindt Lastlogindate
  4         , COUNT ( * )  Cnt
  5      FROM ( SELECT Mindt + ( LEVEL - 1 ) Logindt
  6               FROM Ldates
  7            CONNECT BY LEVEL <= (Ldates.Maxdt - Ldates.Mindt)+1) L
  8         , Authors A
  9     WHERE A.Lastlogindate(+) <= L.Logindt
 10  GROUP BY Logindt
 11  ORDER BY 1 DESC
 12  /

LASTLOGINDATE               CNT
-------------------- ----------
17-Sep-2014                   9
16-Sep-2014                   7
15-Sep-2014                   7
14-Sep-2014                   6
13-Sep-2014                   5
12-Sep-2014                   5
11-Sep-2014                   4
10-Sep-2014                   3
09-Sep-2014                   1
08-Sep-2014                   1

10 rows selected.

[Updated on: Wed, 17 September 2014 10:20] by Moderator

Report message to a moderator

Re: How to find total count based on date [message #624090 is a reply to message #624081] Wed, 17 September 2014 12:09 Go to previous messageGo to next message
pdebasis2013@gmail.com
Messages: 7
Registered: September 2014
Location: Bangalore
Junior Member
Thanks LKBrwn_DBA. Thank you very much.
Its working.
Many thanks for your help once again.

Regards,
Deb
Re: How to find total count based on date [message #624091 is a reply to message #624090] Wed, 17 September 2014 12:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, now you have a query, can you explain it?

Previous Topic: TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number
Next Topic: Performance Issue [Merged]
Goto Forum:
  


Current Time: Fri Mar 29 07:03:12 CDT 2024