Oracle-L: Simple Query

From: Shibu <basher.shibu_at_acusis.com>
Date: Tue, 16 Apr 2002 04:38:22 -0800
Message-ID: <F001.00445233.20020416043822@fatcity.com>

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

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.
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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Shibu
INET: basher.shibu_at_acusis.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Apr 16 2002 - 07:38:22 CDT