Display the sum of queried results.

[leekikwan]

I want to calculate the sum of the 2 queries' outcome.

For example, I would like display the sum of ANCAL and BSCAL.

<cfquery name="NCBMTCOUNT" datasource="abc">
SELECT COUNT(*) ANCAL
FROM NTN.DEMO D, NTN.XPT1 T
WHERE (D.MRN=T.MRN) AND T.XTYPE LIKE 'HT' AND D.HUB = 'NCAL'
</cfquery>

<cfquery name="SCBMTCOUNT" datasource="abc">
SELECT COUNT(*) BSCAL
FROM NTN.DEMO2 D, NTN.XPT2 T
WHERE (D.MRN=T.MRN) AND T.XTYPE LIKE 'CT%' AND D.HUB = 'SCAL'
</cfquery>

Will you help me?

 

[webmigit]

By your question

<Cfset nssum = NCBMTCOUNT.ancal + SCBMTCOUNT.bscal>

should produce what you want?

Tony

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

<cfset thesum = #NCBMTCOUNT.ANCAL#
              + #SCBMTCOUNT.BSCAL#>

you can also do it in one call to the database instead of two:

<cfquery name="COUNTBOTH" datasource="abc">
  SELECT 'ANCAL', COUNT(*) thecount
    FROM NTN.DEMO D
       , NTN.XPT1 T
   WHERE D.MRN=T.MRN
     AND T.XTYPE LIKE 'HT' 
     AND D.HUB = 'NCAL' 
UNION ALL
  SELECT 'BSCAL', COUNT(*) 
    FROM NTN.DEMO2 D
       , NTN.XPT2 T
   WHERE D.MRN=T.MRN
     AND T.XTYPE LIKE 'CT%' 
     AND D.HUB = 'SCAL'
</cfquery>

and then get the combined count with

<cfset thecount = #COUNTBOTH.thecount[1]#
                + #COUNTBOTH.thecount[2]#>

or, if you want to do it generically, i.e. add a third count to the query but not have to change any other code,

<cfset thecount 
  = ArraySum(ListToArray(
          ValueList(#COUNTBOTH.thecount#
                        ))>

rudy

http://r937.com/

SQL Consulting