×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Nested Counting

Nested Counting

Nested Counting

(OP)
Hi all,

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

CODE

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)

CODE

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

CODE

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.

RE: Nested Counting

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.

RE: Nested Counting

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.

RE: Nested Counting

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

Cheers.
Kevin.

RE: Nested Counting

(OP)
Ok, I tried this

CODE

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

CODE

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.

RE: Nested Counting

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close