aggregate functions and group by clause

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.

 

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

 

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.

 

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.

 

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 ?

 

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.

 

Fine..i will mail my problems in the forum itself..thanks