need help with query

[fortune]

the following query gives the number of users per state.thus there are 2 columns that of state and users.i want to add a 3rd column that gives me the count of new users in the past 7 days ,which can be run every week.

select state,count(users)
from a
where username not in (select lgnnm
from b)
and approvaldate > To_date('01-Oct-1999', 'DD-MM-YYYY')
group by state;

how do i go about it?
Thanks in advance

 

[rcurva]

Try this:

select x.state, x.count_all, y.count_7days
from
(select state, count(users) count_all
from a
where username not in (select lgnnm
from b)
and approvaldate > To_date('01-Oct-1999', 'DD-MM-YYYY')
group by state) x,
(select state, count(users) count_7days
from a
where username not in (select lgnnm
from b)
and approvaldate >= sysdate - 7
group by state) y
where x.state = y.state;

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

select state,count(users)
from a
where username not in (select lgnnm
from b)
and approvaldate > sysdate -7
group by state;

 

[carp]

SELECT state, count(*) users, sum(DECODE(trunc(sysdate)-trunc(approval_date),0,1,
1,1,2,1,3,1,4,1,5,1,6,1,7,1,0) new_users
FROM my_table
GROUP BY state;

 

[fortune]

thanks a lot guys!