Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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        .....
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Looks like you need a GROUP BY. Your query above done with a GROUP BY would look like this:

SELECT to_char(auditdate,'YYYY'), count(*) 
FROM AuditLog GROUP BY to_char(auditdate,'YYYY');

A similar query can be used for other groupings. For example, here would be a count for each day:

SELECT to_char(auditdate,'MM/DD/YYYY'), count(*) 
FROM AuditLog GROUP BY to_char(auditdate,'MM/DD/YYYY');

To do this for each PageTitle, simply add PageTitle like this:

SELECT PageTitle , to_char(auditdate,'MM/DD/YYYY'), count(*) 
FROM AuditLog GROUP BY PageTitle, to_char(auditdate,'MM/DD/YYYY');

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:

SELECT PageTitle 
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/22/2012',1,NULL)) Oct_22
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/23/2012',1,NULL)) Oct_23
FROM AuditLog GROUP BY PageTitle;

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:

SELECT 'SELECT PageTitle ' SQL FROM dual
UNION ALL
SELECT DISTINCT '    , Sum(DECODE(to_char(AuditDate,''MM/DD/YYYY''),'''
   || to_char(AuditDate,'MM/DD/YYYY') || ''',1,NULL)) ' || to_char(AuditDate,'Mon_DD')
FROM AuditLog
UNION ALL
SELECT 'FROM AuditLog GROUP BY PageTitle;' FROM dual;

Here is the sample data I used:

drop table auditlog;
create table AuditLog as (select sysdate auditdate, 'Page 1' PageTitle from dual);
insert into auditlog (select sysdate, 'Page 1' from dual);
insert into auditlog (select sysdate, 'Page 2' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 3' from dual);
commit;
share|improve this answer
    
That works great, my only problem is that the results are not showing in the style i need them to. If you look above at my main post i have updated it to show what i need the final table to look like. –  Glen Robson Oct 22 '12 at 14:01
    
You'll need to pivot the data. I'll see if I can create an example. –  Leigh Riffel Oct 22 '12 at 15:21
    
thats great thanks. –  Glen Robson Oct 22 '12 at 15:23
    
Updated answer. –  Leigh Riffel Oct 22 '12 at 16:03
    
That has has helped a lot thanks. –  Glen Robson Oct 22 '12 at 16:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.