Doubt in the database query

[Kamal_Java]

Hi i have two tables called GeneralTable and SpecializedTable which
has Name and State in It LIke
1 .GeneralTable ( GName , State )
2. SpecializedTable( SName , State )

i want to write an SQL query which display the State from where the majority of Person ( both General Table and SpecializedTable ) come from .

Can any one guide me.. i tried many thing but nothing worked . ..

NOte : i know i need to combine them into groups and need to sum , but dont know how to do this in database.

Thanks

[grohit]

Something in these lines, dont have the exact data sample,

Select GName,SName,count(State),State from GeneralTable genTb, SpecializedTable spTb
where genTb.State = spTb.State group by State

If you could give more idea about ur sample data in the two tables and the intended result, it would be easier.

[debasisdas]

is there any relation between those two tables ?

[anubina]

Something in these lines, dont have the exact data sample,

Select GName,SName,count(State),State from GeneralTable genTb, SpecializedTable spTb
where genTb.State = spTb.State group by State

If you could give more idea about ur sample data in the two tables and the intended result, it would be easier.

i suppose that the field gname and sname is not number

-- display the state and number of person in descending order

select g.state,
       count(1) as num_person
  from generaltable g,
       specializedtable s
 where g.state = s.state
 group by g.state
 order by num_person desc,g.state

-- display the states from where the majority of person come from
-- REMEMBER: Is possible that 2 or N states has the same number of person
with

states_person as (
select g.state,
       count(1) as num_person
  from generaltable g,
       specializedtable s
 where g.state = s.state
 group by g.state
 order by num_person desc,g.state
)
select state,
       num_person
  from states_person
 where num_person = (select max(num_person) from states_person)

[ajit1984]

wow thats cool stuff mate...very helpful