Forum OpenACS Q&A: Help with SQL error

Collapse
1: Help with SQL error
Posted by Nick Carroll on 09/26/02 04:29 PM
Hi,

I need some help with an SQL error that I can't nut out.  The
following view fails where I have placed an <ERROR> tag to note
where the error occurs.  The error displayed is "ORA-00979 not a
GROUP BY expression".  I'm new to SQL, and would have thought the
following is valid.

create or replace view historical_visits_new
as
select date_id, n_sessions_day, decode(user_id,null,0,1) member_p,
  ((select <ERROR> 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);

Thanks,
Nick.

Collapse
2: Response to Help with SQL error (response to 1)
Posted by Nick Carroll on 09/26/02 04:31 PM
I apologise for the lack of indentation.  The posting just didn't respond to my tabs.

Nick.

Collapse
3: Response to Help with SQL error (response to 1)
Posted by Jonathan Ellis on 09/26/02 05:42 PM
you need to post in html mode using the PRE tag to do what you want: 
select 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);
since you are doing a subselect there is no need for a group by at all here.
Collapse
4: Response to Help with SQL error (response to 1)
Posted by Brian Fenton on 09/26/02 06:49 PM
Hi Nick,
what is exactly you're trying to do here? It looks like you're trying to count the number of distinct users + browser for each date_id, for each n_sessions_day and for each member_p (which doesn't really make a lot of sense to me). Without knowing the nature of your data, it's hard for me to figure out what you need. Can you describe what each table column contains and maybe even give some example data?

I think maybe you can get around your problem with a few subqueries or even a clever UNION.

Collapse
5: Response to Help with SQL error (response to 1)
Posted by Andrew Piskorski on 09/30/02 05:08 PM
Nick, Brian's right, it's not terribly clear what you're trying to do with that query: 

select
  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.