CODEWITHSUNDEEP
sqlreporting-servicessql-server-2012reportingpercentile
QuestionQuestion
QuestionQuestion

Reputation: 37

SQL Server 2012 Function "Percentile_Cont" - GROUP BY issue

I have been trying to calculate the 50th percentile of a field all day and I think I am getting close, but I am a little stuck.

This is the normal code, without percentile_cont:

declare @st_date datetime;
declare @en_date datetime;
declare @days int;

set @en_date = (@en_datein);
set @st_date = (@st_datein);

select 
    srt.Name,
    cast(sum(sr.price) as int) as AvgCost,
    cast(sum(sr.cost) as int) as AvgTransCost,
    cast(avg(sr.TotalTimeSpent) as int) as TotalTimeSpent
from 
    ServiceReq sr, ServiceReqTemplate srt
where 
    sr.SvcReqTmplLink_RecID = srt.RecId
    and sr.CreatedDateTime >= @st_date
    and sr.CreatedDateTime <= @en_date
group by 
    srt.Name
order by 
    1

This is my code when I add percentile_cont:

declare @st_date datetime;
declare @en_date datetime;
declare @days int;

set @en_date = (@en_datein);
set @st_date = (@st_datein);

SELECT srt.Name,
    percentile_cont(.5) WITHIN GROUP(ORDER BY sr.price) OVER(PARTITION BY srt.Name) AS MedianSpend,
    cast(sum(sr.price) as int) as AvgCost,
    cast(sum(sr.cost) as int) as AvgTransCost,
    cast(avg(sr.TotalTimeSpent) as int) as TotalTimeSpent
from 
    ServiceReq sr, ServiceReqTemplate srt
where 
    sr.SvcReqTmplLink_RecID = srt.RecId
    and sr.CreatedDateTime >= @st_date
    and sr.CreatedDateTime <= @en_date
group by 
    srt.Name
order by 
    1

If I try and execute this I receive the error:

Column sr.price is invalid in the select list because is not contained in either an aggregate function or the GROUP BY clause

I have googled this error and have been reading up on StackOverFlow but I have yet to solve this issue. I tried adding a second Group By, but I'm confused as to which variable I'm supposed to use or if I am even doing this correctly!!

How do I make the percentile_cont code work with the cast code under the same Select statement??

Upvotes: 1

Views: 510

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 1541

I am not sure it will give you the desired output or not but it will fix the error. try with this 

percentile_cont(.5) WITHIN GROUP(ORDER BY sum(sr.price)) OVER(PARTITION BY srt.Name) AS MedianSpend

Complete statement

declare @st_date datetime;
declare @en_date datetime;
declare @days int;

set @en_date = (@en_datein);
set @st_date = (@st_datein);

SELECT srt.Name,
    percentile_cont(.5) WITHIN GROUP(ORDER BY sum(sr.price)) OVER(PARTITION BY srt.Name) AS MedianSpend,
    cast(sum(sr.price) as int) as AvgCost,
    cast(sum(sr.cost) as int) as AvgTransCost,
    cast(avg(sr.TotalTimeSpent) as int) as TotalTimeSpent
from 
    ServiceReq sr, ServiceReqTemplate srt
where 
    sr.SvcReqTmplLink_RecID = srt.RecId
    and sr.CreatedDateTime >= @st_date
    and sr.CreatedDateTime <= @en_date
group by 
    srt.Name
order by 
    1

Upvotes: 1

Related Questions