How to find total count based on date [message #624048] |
Wed, 17 September 2014 07:45 |
|
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 #624053 is a reply to message #624048] |
Wed, 17 September 2014 08:02 |
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 |
|
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 #624063 is a reply to message #624060] |
Wed, 17 September 2014 08:39 |
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 |
|
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 #624081 is a reply to message #624071] |
Wed, 17 September 2014 10:19 |
|
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
|
|
|
|
|