Error in SELECT:cant perform aggregate in subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Error in SELECT:cant perform aggregate in subquery

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-03-18 : 16:43:31

I'm using the following in a select statement:

,CASE measure_code When 'PC-01' Then cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'D' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric)
Else cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'E' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric)

END as perf_visits_score

but I get the following error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can I get around this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 16:52:46

Need the entire query to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-03-19 : 10:00:52

[quote]Originally posted by tkizer

Need the entire query to help.

Here's the full query:

Select Hosp_id
,measure_id
,measure_period_id
,'Assigned' as patient_rollup_type
,'Provider' as measure_rollup_code
,Hosp_id AS rollup_entity_id -- KM@20141212 --> attending provider id replaced with hosp_id due to provider visit multiple hospital.
,etl_date as thru_date
,attending_provider_id as provider_id
,null as practice_id
,null as section_id
,1 as institution_id
,null as part_visits_numerator
,null as part_visits_denominator
,null as part_visits_score
,null as part_patient_numerator
,null as part_patient_denominator
,null as part_patient_score

,count (CASE measure_code When 'PC-01'
Then count (CASE WHEN measure_status = 'D'
Then 1
END
)
ELSE count ( CASE
WHEN measure_status = 'E'
Then 1
END
)
END
) as perf_visits_numerator

,Count (patient_id) as perf_visits_denominator

,CASE measure_code When 'PC-01' Then cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'D' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric)
Else cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'E' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric)

END as perf_visits_score

From [rdc_WZ].[dbo].[wz_cm_Hospbase_Measure_Details]

Where measure_code in (/*'IMM-2', 'STK-8', 'VTE-1', 'VTE-2', 'VTE-5',*/ 'PC-01')
and attending_provider_id is not null

Group by attending_provider_id
,Hosp_id
,measure_id
,measure_period_id
,etl_date
,etl_job_id
,etl_source_id

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2015-03-19 : 10:13:25

Code is failing at the below statement :

,count (CASE measure_code When 'PC-01'
Then count (CASE WHEN measure_status = 'D'
Then 1
END
)
ELSE count ( CASE
WHEN measure_status = 'E'
Then 1
END
)
END
) as perf_visits_numerator

Hema Sunder

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-03-19 : 10:20:32

Is there a different method to code that logic?

quote:
Originally posted by sunder.bugatha

Code is failing at the below statement :

,count (CASE measure_code When 'PC-01'
Then count (CASE WHEN measure_status = 'D'
Then 1
END
)
ELSE count ( CASE
WHEN measure_status = 'E'
Then 1
END
)
END
) as perf_visits_numerator

Hema Sunder

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 12:01:11

Might help if you describe what it is supposed to do?

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-03-19 : 13:43:17

Its suppose to get a count of all the measure_status when it's 'D' if measure_code is 'PC-01' ELSE when it's 'E' it should just do a count. That would represent my erf_visits_numerator column

The next column perf_visits_score, I I need it to do the following:


,cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'E' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric) as perf_visits_score

BUT if if measure_code is 'PC-01' Then I need the following:


,cast(((convert(decimal(3,1),count(CASE WHEN measure_status = 'D' Then 1 END ))) / (Convert(decimal(3,1),Count (patient_id)))* 100)as numeric) as perf_visits_score

Any suggestions?

quote:
Originally posted by Kristen

Might help if you describe what it is supposed to do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources