Nested Counting

[Elroacho]

Hi all,

I have the following SQL statement that works fine...

SELECT distinct ANYBODYPAT.NHINO NHINO,
    ANYBODYPAT.USERID USERID,
    ANYBODYPAT.ETHCODE ETHCODE,
    ANYBODYPAT.GENCODE GENCODE,
    ANYBODYPAT.DOB DOB,
    CLASSIFICATION.READCODE READCODE,
    ANYBODYPAT.ISCAREPLUS ISCAREPLUS,
    ANYBODYPAT.SERPROVCODE SERPROVCODE,
    GEOCODE.QUINTILE DEP
FROM
(((  CLASSIFICATION
  LEFT JOIN USER ANYBODYPAT ON ANYBODYPAT.USERID= CLASSIFICATION.USERID)
  LEFT JOIN ANYBODY ANYBODYANY ON ANYBODYANY.ANYBODYID = CLASSIFICATION.USERID)
  LEFT JOIN  GEOCODE ON GEOCODE.ANYBODYID = ANYBODYPAT.USERID)
WHERE CLASSIFICATION.ROWINACTIVE = 0 AND
    (CLASSIFICATION.READCODE = 'C10.00'   OR
     CLASSIFICATION.READCODE = 'G3.00'    OR
     CLASSIFICATION.READCODE = 'G66.00'   OR
     CLASSIFICATION.READCODE = 'H3.00'    OR
     CLASSIFICATION.READCODE = 'H33.00'   OR
     CLASSIFICATION.READCODE = 'G65.00'   OR
     CLASSIFICATION.READCODE = 'G20.00'   OR
     CLASSIFICATION.READCODE = 'N330.00'  OR
     CLASSIFICATION.READCODE = 'C04.00'   OR
     CLASSIFICATION.READCODE = 'G580.00'  OR
     CLASSIFICATION.READCODE = 'E2B.00'   OR
     CLASSIFICATION.READCODE = 'C3135.00' OR
     CLASSIFICATION.READCODE = 'C324.00')
     AND
     CLASSIFICATION.WHENCLASS IS NOT NULL AND
     ANYBODYPAT.ENROLMENTFUNDINGCODE = 'F' AND
     ANYBODYPAT.ETHCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.DOB IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE <> '' AND
     ANYBODYPAT.USERID IS NOT NULL AND
     ANYBODYPAT.NHINO IS NOT NULL AND
     ANYBODYPAT.NHINO <> ''

the produces a reults like this (I have supressed some of the columns to make it easier)

NHINO   | READCODE| ISCAREPLUS| SERPROVCODE
------------------------------------------
123     | C10.00  |      Y    |   bob
123     | H33.00  |      Y    |   bob
123     | G65.00  |      Y    |   bob
456     | C10.00  |      N    |   bill
456     |  H3.00  |      N    |   bill
789     | H33.00  |      Y    |   ted
789     | G65.00  |      Y    |   ted
853     | C10.00  |      N    |   fred
853     |  H3.00  |      Y    |   fred

my problem is I need to add a column that counts the number of times an NHINO has one of these codes. So therefore in this case it will look like this...

NHINO   | READCODE| ISCAREPLUS| SERPROVCODE | CODECOUNT
-----------------------------------------------------
123     | C10.00  |      Y    |   bob       |    3
123     | H33.00  |      Y    |   bob       |    3  
123     | G65.00  |      Y    |   bob       |    3
456     | C10.00  |      N    |   bill      |    2
456     |  H3.00  |      N    |   bill      |    2
789     | H33.00  |      Y    |   ted       |    2
789     | G65.00  |      Y    |   ted       |    2
853     | C10.00  |      N    |   fred      |    2
853     |  H3.00  |      Y    |   fred      |    2

Any ideas?

Cheers,
Kevin.

PS. My apologies for posting this on the ANSI SQL forum.

 

[bmarks]

You could put a subselect clause in the select clause, kinda like:


SELECT distinct ANYBODYPAT.NHINO NHINO,
ANYBODYPAT.USERID USERID,
ANYBODYPAT.ETHCODE ETHCODE,
ANYBODYPAT.GENCODE GENCODE,
ANYBODYPAT.DOB DOB,
CLASSIFICATION.READCODE READCODE,
ANYBODYPAT.ISCAREPLUS ISCAREPLUS,
ANYBODYPAT.SERPROVCODE SERPROVCODE,
GEOCODE.QUINTILE DEP,
(SELECT COUNT(C2.USERID)
FROM CLASSIFICATION C2
LEFT OUTER JOIN ....
WHERE .....
AND C2.USERID = CLASSIFICATION.USERID) AS CODECOUNT
FROM
...rest of your query...

replace the .... in the subquery with the equivalent pieces from your query.




It might be more efficient to use a stored procedure, but if you can't use a stored procedure, then this will work.

 

[bmarks]

I just realized that you have a "distinct" in your query. Thus you may need to replace the COUNT(C2.USERID) with a COUNT(distinct <field>) and use a field that you do a distinct count on. If there isn't one, then you would need to do a GROUP BY in the subselect on the fields you have in the select statement.

 

[Elroacho]

Thank for that, I will give it a go and let you know the result.

Cheers.
Kevin.

 

[Elroacho]

Ok, I tried this

SELECT distinct ANYBODYPAT.NHINO NHINO,
    ANYBODYPAT.PATIENTID PATIENTID,
    ANYBODYPAT.ETHCODE ETHCODE,
    ANYBODYPAT.GENCODE GENCODE,
    ANYBODYPAT.DOB DOB,
    CLASSIFICATION.READCODE READCODE,
    ANYBODYPAT.ISCAREPLUS ISCAREPLUS,
    ANYBODYPAT.SERPROVCODE SERPROVCODE,
    GEOCODE.QUINTILE DEP,
    (SELECT COUNT(DISTINCT C2.READCODE)
     FROM (((CLASSIFICATION C2
     LEFT OUTER JOIN PATIENT ANYBODYPAT ON ANYBODYPAT.PATIENTID = CLASSIFICATION.PATIENTID)
     LEFT OUTER JOIN ANYBODY ANYBODYANY ON ANYBODYANY.ANYBODYID = CLASSIFICATION.PATIENTID)
     LEFT OUTER JOIN  GEOCODE ON GEOCODE.ANYBODYID = ANYBODYPAT.PATIENTID)
     WHERE CLASSIFICATION.ROWINACTIVE = 0 AND
          (CLASSIFICATION.READCODE = 'C10.00'   OR
           CLASSIFICATION.READCODE = 'G3.00'    OR
           CLASSIFICATION.READCODE = 'G66.00'   OR
           CLASSIFICATION.READCODE = 'H3.00'    OR
           CLASSIFICATION.READCODE = 'H33.00'   OR
           CLASSIFICATION.READCODE = 'G65.00'   OR
           CLASSIFICATION.READCODE = 'G20.00'   OR
           CLASSIFICATION.READCODE = 'N330.00'  OR
           CLASSIFICATION.READCODE = 'C04.00'   OR
           CLASSIFICATION.READCODE = 'G580.00'  OR
           CLASSIFICATION.READCODE = 'E2B.00'   OR
           CLASSIFICATION.READCODE = 'C3135.00' OR
           CLASSIFICATION.READCODE = 'C324.00')
           AND
           CLASSIFICATION.WHENCLASS IS NOT NULL AND
           ANYBODYPAT.ENROLMENTFUNDINGCODE = 'F' AND
           ANYBODYPAT.ETHCODE IS NOT NULL AND
           ANYBODYPAT.GENCODE IS NOT NULL AND
           ANYBODYPAT.DOB IS NOT NULL AND
           ANYBODYPAT.GENCODE IS NOT NULL AND
           ANYBODYPAT.GENCODE <> '' AND
           ANYBODYPAT.PATIENTID IS NOT NULL AND
           ANYBODYPAT.NHINO IS NOT NULL AND
           ANYBODYPAT.NHINO <> ''
           AND C2.PATIENTID = CLASSIFICATION.PATIENTID
           GROUP BY C2.READCODE
    ) CDCount
FROM
(((  CLASSIFICATION
  LEFT JOIN PATIENT ANYBODYPAT ON ANYBODYPAT.PATIENTID = CLASSIFICATION.PATIENTID)
  LEFT JOIN ANYBODY ANYBODYANY ON ANYBODYANY.ANYBODYID = CLASSIFICATION.PATIENTID)
  LEFT JOIN  GEOCODE ON GEOCODE.ANYBODYID = ANYBODYPAT.PATIENTID)
WHERE CLASSIFICATION.ROWINACTIVE = 0 AND
    (CLASSIFICATION.READCODE = 'C10.00'   OR
     CLASSIFICATION.READCODE = 'G3.00'    OR
     CLASSIFICATION.READCODE = 'G66.00'   OR
     CLASSIFICATION.READCODE = 'H3.00'    OR
     CLASSIFICATION.READCODE = 'H33.00'   OR
     CLASSIFICATION.READCODE = 'G65.00'   OR
     CLASSIFICATION.READCODE = 'G20.00'   OR
     CLASSIFICATION.READCODE = 'N330.00'  OR
     CLASSIFICATION.READCODE = 'C04.00'   OR
     CLASSIFICATION.READCODE = 'G580.00'  OR
     CLASSIFICATION.READCODE = 'E2B.00'   OR
     CLASSIFICATION.READCODE = 'C3135.00' OR
     CLASSIFICATION.READCODE = 'C324.00')
     AND
     CLASSIFICATION.WHENCLASS IS NOT NULL AND
     ANYBODYPAT.ENROLMENTFUNDINGCODE = 'F' AND
     ANYBODYPAT.ETHCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.DOB IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE <> '' AND
     ANYBODYPAT.PATIENTID IS NOT NULL AND
     ANYBODYPAT.NHINO IS NOT NULL AND
     ANYBODYPAT.NHINO <> ''

but got the following error

invalid request BLR at offser 1009 
contect already in use (BLR Error)

not sure what this means as the only info I have found on the net is quite generic.

Any thoughts.

 

[bmarks]

You need to use unique aliases for each table in the subselect, so that they are distinguishable from the ones in the select.

That is,

(SELECT COUNT(DISTINCT C2.READCODE)
FROM (((CLASSIFICATION CLASSIFICATION2
LEFT OUTER JOIN PATIENT ANYBODYPAT2 ON ANYBODYPAT2.PATIENTID = CLASSIFICATION2.PATIENTID)
LEFT OUTER JOIN ANYBODY ANYBODYANY2 ON ANYBODYANY2.ANYBODYID = CLASSIFICATION2.PATIENTID)
LEFT OUTER JOIN GEOCODE GEOCODE 2 ON GEOCODE2.ANYBODYID = ANYBODYPAT2.PATIENTID)
WHERE CLASSIFICATION2.ROWINACTIVE = 0 AND
(CLASSIFICATION2.READCODE = 'C10.00' OR
CLASSIFICATION2.READCODE = 'G3.00' OR
CLASSIFICATION2.READCODE = 'G66.00' OR
CLASSIFICATION2.READCODE = 'H3.00' OR
CLASSIFICATION2.READCODE = 'H33.00' OR
CLASSIFICATION2.READCODE = 'G65.00' OR
CLASSIFICATION2.READCODE = 'G20.00' OR
CLASSIFICATION2.READCODE = 'N330.00' OR
CLASSIFICATION2.READCODE = 'C04.00' OR
CLASSIFICATION2.READCODE = 'G580.00' OR
CLASSIFICATION2.READCODE = 'E2B.00' OR
CLASSIFICATION2.READCODE = 'C3135.00' OR
CLASSIFICATION2.READCODE = 'C324.00')
AND
CLASSIFICATION2.WHENCLASS IS NOT NULL AND
ANYBODYPAT2.ENROLMENTFUNDINGCODE = 'F' AND
ANYBODYPAT2.ETHCODE IS NOT NULL AND
ANYBODYPAT2.GENCODE IS NOT NULL AND
ANYBODYPAT2.DOB IS NOT NULL AND
ANYBODYPAT2.GENCODE IS NOT NULL AND
ANYBODYPAT2.GENCODE <> '' AND
ANYBODYPAT2.PATIENTID IS NOT NULL AND
ANYBODYPAT2.NHINO IS NOT NULL AND
ANYBODYPAT2.NHINO <> ''
AND CLASSIFICATION2.PATIENTID = CLASSIFICATION.PATIENTID
GROUP BY C2.READCODE
) CDCount