Results 1 to 4 of 4

Thread: Query help !!!!

  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: Query help !!!!

    hi


    i am trying to make a query but end up at different result .
    attached file has the code for table creation and data insertion .


    the question is :

    how to the address of branch that has most employees (eid) ?


    i tried something like :

    select badd,count(eid)
    from executor e , branch b
    where e.bid=b.bid
    group by badd,eid;


    ===============

    waiting for your help

    thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one, straightforward solution.

    What we have?
    Code:
    SQL> select * from branch order by bid;
    
      BID BADD                                               CITY
    ----- -------------------------------------------------- --------------------
       14 prague city                                        sydney
       56 milly road                                         brisbane
       63 lingham road                                       melbourne
       87 treat street                                       brisbane
       90 mercury street                                     sydney
    
    SQL> select * from executor order by bid;
    
      EID   BID
    ----- -----
     4155    14
     4232    56
     4198    63
     4177    87
     4112    90
     4115    90
    
    6 rows selected.
    How many executors are there in every branch?
    Code:
    SQL> select e.bid, count(*) cnt
      2  from executor e
      3  group by e.bid
      4  order by 2 desc;
    
      BID   CNT
    ----- -----
       90     2
       56     1
       63     1
       87     1
       14     1
    
    SQL>
    Right, we need branch 90 (as it contains 2 executors) - in other words: take a branch whose COUNT equals MAXIMUM COUNT per branch. Something like this:
    Code:
    SQL> select b.bid, b.badd, B.city
      2  from branch b
      3  where b.bid in (select x.bid
      4                  from (select e.bid, count(*) cnt
      5                        from executor e
      6                        group by e.bid
      7                       ) x
      8                  where x.cnt = (select max(count(*))
      9                                 from executor e
     10                                 group by e.bid
     11                                )
     12                 );
    
      BID BADD                                               CITY
    ----- -------------------------------------------------- --------------------
       90 mercury street                                     sydney
    
    SQL>

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    for posted test data, what is expected/desired results & why?
    What should occur when 2 branches have same count?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Oct 2009
    Posts
    2
    wow , thank you very much for your help


    Quote Originally Posted by Littlefoot View Post
    Here's one, straightforward solution.

    What we have?
    Code:
    SQL> select * from branch order by bid;
    
      BID BADD                                               CITY
    ----- -------------------------------------------------- --------------------
       14 prague city                                        sydney
       56 milly road                                         brisbane
       63 lingham road                                       melbourne
       87 treat street                                       brisbane
       90 mercury street                                     sydney
    
    SQL> select * from executor order by bid;
    
      EID   BID
    ----- -----
     4155    14
     4232    56
     4198    63
     4177    87
     4112    90
     4115    90
    
    6 rows selected.
    How many executors are there in every branch?
    Code:
    SQL> select e.bid, count(*) cnt
      2  from executor e
      3  group by e.bid
      4  order by 2 desc;
    
      BID   CNT
    ----- -----
       90     2
       56     1
       63     1
       87     1
       14     1
    
    SQL>
    Right, we need branch 90 (as it contains 2 executors) - in other words: take a branch whose COUNT equals MAXIMUM COUNT per branch. Something like this:
    Code:
    SQL> select b.bid, b.badd, B.city
      2  from branch b
      3  where b.bid in (select x.bid
      4                  from (select e.bid, count(*) cnt
      5                        from executor e
      6                        group by e.bid
      7                       ) x
      8                  where x.cnt = (select max(count(*))
      9                                 from executor e
     10                                 group by e.bid
     11                                )
     12                 );
    
      BID BADD                                               CITY
    ----- -------------------------------------------------- --------------------
       90 mercury street                                     sydney
    
    SQL>

Posting Permissions

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