# Issue with average

Hi All,

With the following code, I have not managed to add the name the department which has the highest average, If I add the DEPT field on the very last row, suddenly I get much more rows than needed, How could I display the TFC, Max Average, DEPT having the max average?

Many Thanks

``````Select
Verylong_q.TFC,
Round(MAX(verylong_q.average),2) AS HIGHEST_AVERAGE
FROM

(
SELECT
Long_Q.TFC,
Long_Q.DEPT,
Long_Q.Percentage1,
Long_Q.Percentage2,
Long_Q.Percentage3,
((Percentage1 + Percentage2 + Percentage3)/3) AS Average
FROM
(
SELECT
t_Month1.TFC,
t_Month1.DEPT,
t_Month1.Percentage1,
t_Month2.Percentage2,
t_Month3.Percentage3
From
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage3
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month1
LEFT JOIN
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage2
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month2
On t_month1.DEPT = t_month2.DEPT and t_month1.TFC = t_month2.TFC
LEFT JOIN
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage3
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month3
on t_month1.DEPT = t_month3.DEPT and t_month1.TFC = t_month3.TFC
) Long_Q
) VeryLong_Q
Group by verylong_q.TFC``````

I think this will get you in the right direction (it has not been tested, as you didn't provide sample data to work with):

``````with cte1
as (select p.tfc
,m.dept
,(sum(case
when p.[date]>=cast('2016-01-01' as date) /* january 2016 */
and p.[date]<cast('2016-02-01' as date)
then p.percentage
else 0
end
)
+sum(case
when p.[date]>=cast('2016-02-01' as date) /* february 2016 */
and p.[date]<cast('2016-03-01' as date)
then p.percentage
else 0
end
)
+sum(case
when p.[date]>=cast('2016-03-01' as date) /* march 2016 */
and p.[date]<cast('2016-04-01' as date)
then p.percentage
else 0
end
)
)/3
as average
from tbo_pos as p
inner join tbl_mv as m
on m.iv_id=p.iv_id
and p.[date]>=cast('2016-01-01' as date) /* january to march 2016 */
and p.[date]<cast('2016-04-01' as date)
where p.tfc in (...) /* please fill in */
group by p.tfc
,m.dept
)
,cte2
as (select tfc
,dept
,average
,row_number() over(partion by tfc order by average desc) as rn
from cte1
)
select tfc
,dept
,round(average,2) as highest_average
from cte2
where rn=1
;
``````

Many Thanks Bitsmed! This has helped me!