Im currently working on a project that uses Oracle 10g where every action from the user is recorded in a auditlog table. The current table holds the following values:
AuditID
AuditDate
Username
Page Name
Type (Insert, Delete, Update, Login etc...)
Whenever a user does one of the above Types an audit log is saved.
What I would like to do is to get a count of each page for a particular time scale. For example Day, Month, Year and Quarter.
I have came up with some SQL to get the results for all of the pages together but i am having some trouble with getting the values for each individual page. Below i have given one of the SQL queries that i am using (Year).
SELECT (SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-1100, 'yy')) As Y_2009,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-732, 'yy')) As Y_2010,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)-368, 'yy')) As Y_2011,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate), 'yy')) As Y_2012,
(SELECT Count(*) from AUDITLOG WHERE to_char(auditdate, 'yy') = to_char(to_date(sysdate)+368, 'yy')) As Y_2013
From Dual;
This SQL would give me the results of every single page for each year. However I need to find out the results for each page for each year. Is there anyway of doing this without creating an SQL statement like the one above for each page?
If you would like more information please ask.
Edit
The results i need would need to be in the following format:
PageTitle| Oct_1 Oct_2 Oct_3 Oct_4 Etc...
----------------------------------------------------------
Page1 | 12 23 28 43 .....
Page2 | 23 64 21 18 .....
Best Answer
Looks like you need a GROUP BY. Your query above done with a
GROUP BY
would look like this:A similar query can be used for other groupings. For example, here would be a count for each day:
To do this for each PageTitle, simply add PageTitle like this:
To pivot the data as you have shown gets a bit more difficult particularly on 10g where there is no
PIVOT
. Here is one way to do it on 10g:Of course this requires entering all the dates. You can make this a bit easier by generating the query using a query such as this:
Here is the sample data I used: