Fw: Simple Query

[Shibu]

santosh:   Thanks for the help    The following query also worked   fine  .    Select     count(tc_transcriptid) file_count , Sum(Tc_LineCount),                 trunc(tc_collectdatetime)  From       dc_transcript_collect  where      (Tc_TranscriptId,Tc_ActionId)   in           (Select Tc_TranscriptId,Max(TC_ActionId)   From     dc_transcript_collect  Group By Tc_TranscriptId) group by  trunc(tc_collectdatetime)     regards, shibu   ----- Original Message ----- From: Santosh Varma To: Multiple recipients of list ORACLE-L Sent: Friday, April 19, 2002 12:13 PM Subject: RE: Simple Query Hi shibu,   Try the following query ->> I hope this helps.   SELECT count(*),sum(TC_LINECOUNT) from tablename.a,tablename.b where TC_ACTIONID = (select MAX(ACTIONID) from tablename.b where b.TC_TRANSCRIPTID in (select DISTINCT TC_TRANSCRIPTID from tablename.a))   santosh -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Shibu Sent: Tuesday, April 16, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Subject: Simple Query Hi All,               I have a specific problem in retrieving rows from a table. I am listing down the some sample data in a table.       TC_ID         TC_TRANSCRIPTID         TC_COLLECTDATETIME             TC_ACTIONID                 TC_LINECOUNT     --------          ----------------------------          -----------------------------------               --------------------                  ------------------------         1                       101                        04/02/2002 12:30:00                        1060                                   10         2                       101                        04/02/2002 01:00:00                        1080                                    8        -------->          3                       102                        04/02/2002 02:00:00                        1060                                   25         4                       102                        04/02/2002 03:00:00                        1080                                   27       --------->         5                       103                        04/02/2002 04:00:00                        1060                                   40       ---------->   I need a query which will retrieve the COUNT(TC_ID) and SUM(TC_LINECOUNT) and the Conditions are   1) COUNT(TC_ID) should only be retrieved for the maximum of TC_ACTIONID. ( I mean for all those TC_TRANSCRIPTID which has got more than one entries in the table the maximum of TC_ACTIONID should be retrieved. 2) SUM(TC_LINECOUNT) should only be retrieved for the maximum of TC_ACTIONID   I mean each and every TC_TRANSCRIPTID can have 'n' number of records in the table and when the query is executed it should retrieve the MAXIMUM of TC_ACTIONID that a TC_TRANSCRIPTID is having and the same goes with the File Count also.   In the example sited above the query should retrieve COUNT(TC_ID) as 3 and the SUM(TC_LINECOUNT) as 75.   COUNT(TC_ID)            SUM(TC_LINECOUNT) -----------------------            ---------------------------------           3                                       75     Thanks in advance.   Regards,   Shibu.   Acusis - Bangalore