Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Doubt in the database query .

    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 query

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Get the MAX of the COUNT by GROUP.

    Quote Originally Posted by java_kamal
    .. i tried many thing but nothing worked . ..
    What did you try?

    HINT: You may need to use; GROUP BY, COUNT() and MAX() functions.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    doubt in dbs query

    I Tried this

    select SUM ( Count( NC.state) + Count(S.state) ) from GeneralTable NC , SpecializedTable s WHERE NC.STATE = S.STATE group by ( NC.STATE ) ;


    and tried with UNION too ..

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Ok,

    Here is one way to do it:
    Code:
    SELECT *
      FROM (SELECT   state, SUM (g_cnt + c_cnt) cnt,
                     ROW_NUMBER () OVER (ORDER BY SUM (g_cnt + c_cnt) DESC) rn
                FROM (SELECT   g.state, COUNT (*) g_cnt, 0 s_cnt
                          FROM generaltable g
                      GROUP BY state
                      UNION
                      SELECT   s.state, 0, COUNT (*)
                          FROM specializedtable s
                      GROUP BY state)
            GROUP BY state)
     WHERE rn = 1;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •