Oracle-L: RE: Simple Query

From: Santosh Varma <vsantosh_at_psi.soft.net>
Date: Thu, 18 Apr 2002 22:43:18 -0800
Message-ID: <F001.0044911B.20020418224318@fatcity.com>

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: root_at_fatcity.com [mailto:root_at_fatcity.com]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

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: Santosh Varma
INET: vsantosh_at_psi.soft.net

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 Fri Apr 19 2002 - 01:43:18 CDT