calculating average on expression containing aggregate

[mjd3000]

I am trying to find the average of a count on a field. Can anybody tell me what is wrong with my SQL?

select new_programmemoidname as 'Programme', new_programcontracouttidname as 'Programme Contract',
new_outputtitlemthidname as 'Output Name', new_monthlyprofileoutidname as 'Month',
new_numberofoutputs as 'Target', COUNT(e.new_evidenceid) as 'Actual',

avg(evcount) from
(select COUNT(e.new_evidenceid) evcount
from FilteredNew_monthlyoutput m
left join FilteredNew_evidence e
on m.new_monthlyprofileoutid = e.new_claimperiodid
AND m.new_monthlyoutputsid = e.new_evidenceforoutputid
where m.statecode = 0
and m.new_enddateutc < CURRENT_TIMESTAMP
and new_programmemoidname like 'MCC RSL%'
and new_programcontracouttidname = 'No Programme Contract'
group by new_programmemoidname, new_programcontracouttidname, new_outputtitlemthidname,
new_monthnumber, new_monthlyprofileoutidname, new_numberofoutputs) as 'Average',

COUNT(e.new_evidenceid) - new_numberofoutputs as 'Variance'
from FilteredNew_monthlyoutput m
left join FilteredNew_evidence e
on m.new_monthlyprofileoutid = e.new_claimperiodid
AND m.new_monthlyoutputsid = e.new_evidenceforoutputid
where m.statecode = 0
and m.new_enddateutc < CURRENT_TIMESTAMP
and new_programmemoidname like 'MCC RSL%'
and new_programcontracouttidname = 'No Programme Contract'

 

[MartinCroft]

Not 100% sure what your trying to achieve, but if its an average for the total count, you need to use a sub query, your code doesn't seem to be doing that. I have shown a simple example below hopefully you can see how the subquery has been used to give an average against the total count and use the principal in your code. It might help posting and example result you are trying to achieve, trying to keep as simple as possible

I.e The code below populate some dummy rows into a table and
returns each row and the avg of column 2

Col1 col2 Avg of sum of col2
1 1 2.500000
2 1 2.500000
3 2 2.500000
4 2 2.500000
5 1 2.500000

CREATE TABLE #TEST (col1 int , col2 int)


INSERT INTO #test(col1, col2) VALUES (1,1)
INSERT INTO #test(col1, col2) VALUES (2,1)
INSERT INTO #test(col1, col2) VALUES (3,2)
INSERT INTO #test(col1, col2) VALUES (4,2)
INSERT INTO #test(col1, col2) VALUES (5,1)


--Rows cast as decimal using * 1.0 to show average of 3 and 2

select count(col1) *1.0 as Total from #test
group by col2



SELECT 
	Col1,
	Col2,
	(SELECT AVG(total)
		FROM
		(
			SELECT 
				count(col1) *1.0 as Total 
			FROM 
				#test
			GROUP BY 
				col2
		) a
	)AvgValue
	
FROM 
	#test

 

[mjd3000]

I've tried this which is in the same format as your SQL, but I get the error 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery' :


(select avg(evcount) from
(select COUNT(e.new_evidenceid) as 'evcount'
from FilteredNew_monthlyoutput m
left join FilteredNew_evidence e
on m.new_monthlyprofileoutid = e.new_claimperiodid
AND m.new_monthlyoutputsid = e.new_evidenceforoutputid
where m.statecode = 0
and m.new_enddateutc < CURRENT_TIMESTAMP
and new_programmemoidname like 'MCC RSL%'
and new_programcontracouttidname = 'No Programme Contract'
group by new_programmemoidname, new_programcontracouttidname, new_outputtitlemthidname,
new_monthnumber) a
) 'Average',

 

[mjd3000]

Hi,

I have this working without an error now, but I want it to return data like this :

Prog PrCont Output Ave
P1 PC1 O1 5
P2 PC12 O2 7
P3 PC4 O2 14

But instead it gives one average across all the Prog/Prog Cont/Output combinations. Here is my code :

select new_programmemoidname as 'Programme', new_programcontracouttidname as 'Programme Contract', 
new_outputtitlemthidname as 'Output Name', new_monthlyprofileoutidname as 'Month', 
new_numberofoutputs as 'Target', COUNT(e.new_evidenceid) as 'Actual', 
  
(select avg(evcount) from 
(select COUNT(e.new_evidenceid) as 'evcount'
from FilteredNew_monthlyoutput m
left join FilteredNew_evidence e
on m.new_monthlyprofileoutid = e.new_claimperiodid
AND m.new_monthlyoutputsid = e.new_evidenceforoutputid
where m.statecode = 0
and m.new_enddateutc < CURRENT_TIMESTAMP
and new_programmemoidname like 'MCC RSL%' 
and new_programcontracouttidname = 'No Programme Contract'
group by new_programmemoidname, new_programcontracouttidname, new_outputtitlemthidname) a
) 'Average',

COUNT(e.new_evidenceid) - new_numberofoutputs as 'Variance'
from FilteredNew_monthlyoutput m
left join FilteredNew_evidence e
on m.new_monthlyprofileoutid = e.new_claimperiodid
AND m.new_monthlyoutputsid = e.new_evidenceforoutputid
where m.statecode = 0
and m.new_enddateutc < CURRENT_TIMESTAMP
and new_programmemoidname like 'MCC RSL%' 
and new_programcontracouttidname = 'No Programme Contract'
group by new_programmemoidname, new_programcontracouttidname, new_outputtitlemthidname, 
new_monthnumber, new_monthlyprofileoutidname, new_numberofoutputs
order by new_programmemoidname, new_programcontracouttidname, new_outputtitlemthidname, 
new_monthnumber, new_numberofoutputs

 

[mjd3000]

Please ignore - I have found an answer to this