1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

aggregate functions and group by clause

Discussion in 'SQL Server' started by rishumakan, May 4, 2009.

  1. rishumakan

    rishumakan New Member

    Joined:
    May 4, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Hi ,- Hide quoted text -


    I would be highly obliged if you please help me in making a SQL query.I am stuck into it since morning.My problem is ,
    I have 2 tables tbl1 and tbl2.
    tbl1 has 2 cols bid(PK) and name (varchar)
    tbl2 has 3 cols bid(FK),ename(varchar),edate(datetime)
    Code:
    data in tbl1 : 1  A
                       2  B
                       3  C etc
    data in tbl2 : 1  aaa  12/12/2008
                       1  bbb  11/12/2008
                       2  ccc  12/12/2008
                       2  ddd  13/12/2008
                       3  ttt    14/12/2008  
    
    I want a result which will have all data from tbl1 and the only that data from tbl2 which will have maximum date( for example aaa for B) + total data for BId of tbl1 from tbl2
    result would be somthing like :
    Code:
    bid   name     ename    edate           TotalData
     1    A             aaa      12/12/2008    2
     2    B             ddd      13/12/2008    2
     3    C             ttt        14/12/2008    1
    
    Please help me out in this problem.
     
    Last edited by a moderator: May 4, 2009
  2. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Check if this works.

    There can be more simpler query, but since I do not have the access to create tables now, this is what I can give.


    Code:
    select t1.bid
         , t1.name
         , t2.ename
         , t3.edate
         , t4.count
      from tbl1 t1
         , tbl2 t2
         , ( select max(a.edate)
               from tbl2 a
              where a.bid = t1.bid
           ) t3
         , ( select count(*) "Count"
               from tbl2 b
              where b.bid = t1.bid
           ) t4
     where t1.bid = t2.bid
     
  3. rishumakan

    rishumakan New Member

    Joined:
    May 4, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Thanks for replying..the query you mailed me gave some error like :

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "t1.bid" could not be bound.
    Msg 8155, Level 16, State 2, Line 1
    No column was specified for column 1 of 't3'.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "t1.bid" could not be bound.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'edate'.

    but this query :

    select t1.bid, t1.name, t2.ename, t2.edate,(select count(*) from tbl2 t4 where t4.bid = t1.bid) as total_Cnt
    from tbl1 t1,tbl2 t2 where t1.bid = t2.bid
    and t2.edate = (select max(edate) from tbl2 t3 where t2.bid = t3.bid)

    worked fine for me..might be i need to do some changes in your query as u didn't have access for making tables.Thanks again for replying.
     
  4. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    I wrote the query based on Oracle because I'm currently using Oracle.
    So, the syntax might be little different if you want to use it on SQL Srever.

    Did you get the desired output from the modified query.
     
  5. rishumakan

    rishumakan New Member

    Joined:
    May 4, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    This might be the reason.Ya that query worked for me.One more thing,can i have your personal email id so that i can mail u directly in your id if will be in need in future ?
     
  6. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    You can have my personal ID but I think the correct place to post your question is the forum itself. That's why forum is made for, to help each other.

    If you contact by email, I may or may not be able to give you correct answer as I'm not an expert myself. But if you post your question in the forum, some or the other expert will help you definitely.
     
  7. rishumakan

    rishumakan New Member

    Joined:
    May 4, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Fine..i will mail my problems in the forum itself..thanks
     

Share This Page