Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
Query Help [message #260112] Fri, 17 August 2007 08:44 Go to next message
kalyan_218
Messages: 4
Registered: August 2007
Junior Member
i have two tables

Table T1 Has following cloums

A B

0 1
0 2
0 3


Table T2 has follwing Columns
A B C
0 1 20070505
0 2 20070505

Here Column A and Column B in the both the tables are same. T2 has all the active users and Table1 has all the users.

my question is I need to find all the active users and also inactive users In the same query group by Column C and Column A

FOr active its just a simple query from TAble B ,How to find inacive user in the same query?

Select
A,
C,
count(distinct(B)) active_usrs
from T2
group by C,A;


Thanks
K

[Updated on: Fri, 17 August 2007 08:54]

Report message to a moderator

Re: Query Help [message #260114 is a reply to message #260112] Fri, 17 August 2007 08:49 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Please format your post first.
For knowing How to Format please see HOW TO FORMAT

Regards
Sanka

[Updated on: Fri, 17 August 2007 08:50]

Report message to a moderator

Re: Query Help [message #260117 is a reply to message #260114] Fri, 17 August 2007 08:57 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

You can use the below query:

Select A,C
,count(distinct(B)) active_usrs,
(select count(a) from t1 x where x.a = y.a)-count(distinct(B)) as Inactive_Users
from T2 y
group by C,A
Re: Query Help [message #260119 is a reply to message #260112] Fri, 17 August 2007 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See outer join.

Regards
Michel
Re: Query Help [message #260126 is a reply to message #260117] Fri, 17 August 2007 09:23 Go to previous messageGo to next message
kalyan_218
Messages: 4
Registered: August 2007
Junior Member
Thanks a lot dude
Re: Query Help [message #260181 is a reply to message #260126] Fri, 17 August 2007 11:57 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummm ... I wouldn't call Michel a dude. Seems a little bit inappropriate. If he was Englishman, I believe he'd be Sir Michel by now.
Previous Topic: User Defined errors
Next Topic: Delete using ranking function
Goto Forum:
  


Current Time: Tue Dec 06 06:10:57 CST 2016

Total time taken to generate the page: 0.24910 seconds