Crosstab question

[jodie]

Okay, I'm a little stumped on this one.

I have been auditing our session logons. Every week, I take all the rows in sys.aud$ and put them into my own table, so aud$ doesn't grow too big. Anyway, I have a row for each connection. And I need a report that looks like this:

ID  JAN  FEB  MAR....
a     2    2    4
b     1    3    5
:
:

Where the values for the month are the number of DAYS that the user logged on, not the number of connections.

Here's my query:

  select username, 
         count(decode(to_char(timestamp,'MON'), 'NOV', trunc(timestamp), NULL)) NOV,
         count(decode(to_char(timestamp,'MON'), 'DEC', trunc(timestamp), NULL)) DEC,
         sum(decode(to_char(timestamp,'MON'), 'JAN', distinct trunc(timestamp), 0)) JAN ... 
    from oradba.audit_session
group by username;

But this gives me the number of connections, which is not what I want. (Our app uses between 3 and 10 connections for every sign on). I need the number of DAYS each month the user logged on.

I tried using "count distinct", but get the error ORA-923 FROM KEYWORD NOT FOUND WHERE EXPECTED.

Any ideas?

Thanks!
Jodie

[slimdave]

...
count(distinct trunc(timestamp))
...

[jodie]

I solved my problem by just creating a view:

CREATE OR REPLACE VIEW ORADBA.AUDIT_SESSION_DAILY 
(
    TIMESTAMP,
    USERNAME
)
AS
select distinct(trunc(timestamp)), username
from oradba.audit_trail
/

and then running my decode off of that view:

select username, 
       sum(decode(to_char(timestamp,'MON'), 'NOV', 1, 0)) NOV,
       sum(decode(to_char(timestamp,'MON'), 'DEC', 1, 0)) DEC,
       sum(decode(to_char(timestamp,'MON'), 'JAN', 1, 0)) JAN ,
       sum(decode(to_char(timestamp,'MON'), 'FEB', 1, 0)) FEB ..
from oradba.audit_session_daily
group by username;

...
count(distinct trunc(timestamp))
...

Dave,
Exaclty where would you put "count(distinct trunc(timestamp))" in the query? If you put it inside the decode, you get "Not a single Group by function." So, what would you group by since it's inside the decode. Maybe what you're suggesting is pretty simple and I've made this more complicated than it needs to be. Can you please shed some light on your suggestion for me. I'd still like to do this without the view if I can.

Thanks!
Jodie

[slimdave]

select
   username, 
   count(distinct decode(to_char(timestamp,'MON'), 'NOV', trunc(timestamp), NULL)) NOV,
   count(distinct decode(to_char(timestamp,'MON'), 'DEC', trunc(timestamp), NULL)) DEC,
...
from
   oradba.audit_session
group by
   username
/

[jodie]

Duh!
Can I blame my stupidity on the late running Super Bowl and Survivor?

Thanks for spoon feeding that to me, Dave.. not sure why I didn't see it myself!

Jodie