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

Recommended Answers

All 4 Replies

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.

is there any relation between those two tables ?

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)

wow thats cool stuff mate...very helpful

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.