multiple "group by" queries in 8i

[SJ Adnams]

I'm trying to come up with an MIS query to summarise the number of updates to a set of rows in the past 3,6 12 months etc.
I came up with this:

select 
  a.row_owner, 
  max(b.full_name),
  count(1) total_rows,
  max(c.period_total) "6 MONTH UPDATES",
  max(d.period_total) "12 MONTH UPDATES"
from 
  accounts a, 
  row_owner_table b,
  (select count(owner_id) period_total, owner_id from accounts  where last_update > ( sysdate - 180) group by owner_id ) c,
  (select count(prd_ow_i) period_total, owner_id from accounts  where last_update > ( sysdate - 360) group by owner_id ) d
where 
  b.id = a.row_owner and
  d.owner_id  (+) =  a.owner_id and
  c.owner_id  (+) =  a.owner_id
group by a.owner_id order by count(1) desc;

unfortunatly the 2nd subquery seems to overwrite the 1st subquery results (bug maybe?) can anyone see why this shouldn't work in
8i? I tried it using 9i and the query works fine.
i.e. in 8i the last 2 columns have identical results, but in 9i they have different (correct) results.
Cheers, Simon

[SJ Adnams]

rewrote it using sum & decode, problem solved.

[Leslie Chaim]

Simon,
That was a very nice SQL, could you please post your solution?
Thanks,

[SJ Adnams]

Sure!

 
select
  distinct(a.user_id)  ID,
  max(b.full_name)     FULL_NAME,
  max(a.location)      LOCATION,
  sum(a.seven_day)     WEEK,
  sum(a.thirty_day)    MONTH,
  sum(a.ninty_day)     QUARTER,
  sum(a.one_year)      YEAR,
  sum(a.all_day)       TOTAL
from (select 
  location, 
  user_id,
  decode(greatest(last_update, sysdate - 7),  least(last_update,sysdate),1,0) seven_day,
  decode(greatest(last_update, sysdate - 30), least(last_update,sysdate),1,0) thirty_day,
  decode(greatest(last_update, sysdate - 90), least(last_update,sysdate),1,0) ninty_day,
  decode(greatest(last_update, sysdate - 360),least(last_update,sysdate),1,0) one_year,
  1 all_day
from
  accounts) a,
  row_owner_table b
where 
  b.user_id = a.user_id 
group by 
  a.user_id,  b.full_name
order by 
  sum(a.seven_day) desc, sum(a.thirty_day) desc;
  
 

[SJ Adnams]

It's probably more efficient then my first solution too.
Can you figure out how not to use the "least()" bit?

[Michael Matola]

I think the "case" statement could help you out here.
Try something along the lines of the following as the basis of your query

select user_id ,
        count( case when last_update > sysdate - 7 then 1 end ) "seven_day" ,
        count( case when last_update > sysdate - 30 then 'SimonLee' end ) "thirty_day" ,
        count( case when last_update > sysdate - 90 then 1 else null end ) "ninty_day"
   from accounts
  group by user_id

You'll probably be able to get rid of the inline view with this approach.
Quick notes on case statement. I *believe* it's the ANSI replacement for decode(), if() and so forth. It's been in Oracle since a few versions back. It's *much* more powerful than decode in that it can test for conditions other than equality. The syntax is a bit wonky: case when <condition> then <expression> [else <expression>] end. You can have an arbitray number of when...then...'s too.
If the condition evaluates true, then the expression as a whole takes the value of the "then" expression. If the condition evaluates false, then the expression as a whole either takes the default value of null or the value of the optional else expression.
In the third case statement above, the "else null" is redundant. I just put it in to help show syntax.
In the second case statement above, the 'SimonLee' shows that the "1" in "then 1" is essentially a dummy value and reminds us how count() works. For a given row, count() evaluates to 1 or 0 depending on whether the value inside count is not null or null. So we have to make the case statement produce not null or null. I chose to use "1" for not null as reminder that I'm counting here, but really anything would do.
Anyhow, not a single least() used!

[Michael Matola]

One other small thing: the distinct() in Simon's solution is redundant, I believe. Any time you have an aggregating function in your select list, everything else is automatically distinct.