Got a error message when I try to find out which patient account have duplicated record.

Question

When I run the script below, I got a error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery" Please provide some advice. Thanks

SELECT 
CONVERT(DECIMAL(18,5),SUM(CASE WHEN PATIENT_ACCOUNT_NO IN (
                                    SELECT PATIENT_ACCOUNT_NO
                                    FROM STND_ENCOUNTER
                                    GROUP BY PATIENT_ACCOUNT_NO
                                    HAVING ( COUNT(PATIENT_ACCOUNT_NO) > 1))  THEN 0 ELSE 1 END)) dupPatNo
FROM [DBO].[STND_ENCOUNTER]

Have you tried to search for the error message? http://stackoverflow.com/questions/15751241/sql-server-cannot-perform-an-aggregate-function-on-an-expression-containing-an — default locale, Jul 23 '14 at 18:11
I think the error message is pretty self explained: You cannot have another `aggregate` function or `subquery` inside an `aggregate` function. Aggregate functions = SUM, MAX, COUNT etc. — C.J., Jul 23 '14 at 18:11

score 3 · Answer 1 · answered Jul 23 '14 at 18:12

3

I think the error message is pretty clear. You have a sum() function with a subquery in it (albeit within a case, but that doesn't matter).

It seems that you want to choose patients that have more than one encounter, then add 0 if the patients is in the list and 1 if the patient is not. Hmmm. . . sounds like you want to count the number of patients with only one encounter.

Try using this logic instead:

select count(*)
from (select se.*, count(*) over (partition by PATIENT_ACCOUNT_NO) as NumEncounters
      from dbo.stnd_encounter se
     ) se
where NumEncounters = 1;

As a note, the variable you are assigning is called DupPatientNo. This sounds like the number of patients that have duplicates. In that case, the query is:

select count(distinct PATIENT_ACCOUNT_NO)
from (select se.*, count(*) over (partition by PATIENT_ACCOUNT_NO) as NumEncounters
      from dbo.stnd_encounter se
     ) se
where NumEncounters > 1;

(Or use count(*) if you want the number of encounters on duplicate patients.)

answered Jul 23 '14 at 18:12

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon for the suggestion. The reason I have the format that I mentioned is because I want to find out which patient account no are duplicated so I can count the number of them then I can based on this count number to do percentage calculation or other calculation. Your point is great also。 format has to be like this CONVERT(DECIMAL(18,5),SUM(CASE WHEN 《something》 THEN 0 ELSE 1 END)) – TDai Jul 23 '14 at 19:43
I would suggest that you ask another question with your more detailed percentage calculation (which seems to have nothing to do with the question that you asked). It is also helpful to include a SQL Fiddle, with sample data. The queries in this answer can readily be adapted to determine which account numbers are duplicated (or not duplicated). – Gordon Linoff Jul 23 '14 at 20:11
Thanks Gordon, I found your answer is very helpful in some way. By the way, since I am new to this site, how do include SQL Fiddle as you said. – TDai Jul 23 '14 at 20:19
@user3645407 . . . Go to www.sqlfiddle.com. There are instructions on the site (basically, choose your database, put table definitions and insert statements on the left, then queries on the right). You can copy a link into questions, answers, and comments so other can see -- and modify -- the SQL. – Gordon Linoff Jul 23 '14 at 21:10

score 0 · Answer 2 · answered Jul 23 '14 at 18:39

If you want to find number of PATIENT_ACCOUNT_NO that does not have any duplicates then use the following

SELECT COUNT(DISTINCT dupPatNo.PATIENT_ACCOUNT_NO)
  FROM (
          SELECT PATIENT_ACCOUNT_NO
            FROM STND_ENCOUNTER
        GROUP BY PATIENT_ACCOUNT_NO
        HAVING COUNT(PATIENT_ACCOUNT_NO) = 1
       ) dupPatNo

If you want to find number of PATIENT_ACCOUNT_NO that have atleast one duplicate then use the following

SELECT COUNT(DISTINCT dupPatNo.PATIENT_ACCOUNT_NO)
  FROM (
          SELECT PATIENT_ACCOUNT_NO
            FROM STND_ENCOUNTER
        GROUP BY PATIENT_ACCOUNT_NO
        HAVING COUNT(PATIENT_ACCOUNT_NO) > 1
       ) dupPatNo

Use of DISTINCT will make the query not count same item again and again

Though your query looks for first result, its not clear what you want. Hence giving query for both

Got a error message when I try to find out which patient account have duplicated record.

2 Answers2