Forum OpenACS Q&A: Response to Help with SQL error

Posted by Andrew Piskorski on
Nick, Brian's right, it's not terribly clear what you're trying to do with that query:

  date_id,  n_sessions_day
  ,decode(user_id,null,0,1) member_p
     ( select count(distinct user_id)
       from historical_visits a
       where a.date_id = v.date_id ) +
     ( select count(distinct browser_id)
       from historical_visits b
       where b.date_id = v.date_id
         and b.user_id is NULL)
   ) n_users
from historical_visits v
group by date_id, n_sessions_day, decode(user_id,null,0,1) ;

Depending on exactly what you're trying to do, Oracle's lead or lag commands might be useful, which are discussed a bit in this old thread.