Member Avatar for 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

  • 5 Contributors
  • 4 Replies
  • 106 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by ajit1984

Dani AI

Generated

— the simplest and correct pattern for "which state contributes the most people" is to stack the two tables, then aggregate. The join examples from and will only count rows where a state appears in both tables and will therefore undercount in the usual case where the tables are independent lists. is right to ask about relationships: if one table is a detail table that references the other, you may need a join; otherwise UNION ALL is the right tool.

Use a CTE (or subquery) to UNION ALL the state values, aggregate per state, then pick the max. This handles ties if you compare counts to the overall maximum:

WITH all_states AS (
  SELECT state FROM GeneralTable
  UNION ALL
  SELECT state FROM SpecializedTable
),
counts AS (
  SELECT state, COUNT(*) AS cnt
  FROM all_states
  GROUP BY state
)
SELECT state, cnt
FROM counts
WHERE cnt = (SELECT MAX(cnt) FROM counts);

If you prefer a single-pass tie-safe solution, compute counts and apply an analytic RANK():

WITH all_states AS (
  SELECT state FROM GeneralTable
  UNION ALL
  SELECT state FROM SpecializedTable
),
state_counts AS (
  SELECT state, COUNT(*) AS cnt
  FROM all_states
  GROUP BY state
)
SELECT state, cnt
FROM (
  SELECT state, cnt, RANK() OVER (ORDER BY cnt DESC) AS rnk
  FROM state_counts
) WHERE rnk = 1;

Notes and caveats: if the same person can appear in both tables and should be counted once, dedupe by a stable person identifier (not by name) before counting (use UNION or SELECT DISTINCT person_id, state ...). Exclude NULL states if needed (WHERE state IS NOT NULL). For large tables prefer UNION ALL (no implicit dedupe) and make sure state is indexed to speed the GROUP BY.

Recommended Answers

All 4 Replies

Member Avatar for 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.

Member Avatar for debasisdas

is there any relation between those two tables ?

Member Avatar for 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)
Member Avatar for ajit1984

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.