Creating summary data from a configuration table in SQL Server

Question

I am trying to create a summary from a score list in SQL Server 2008.
I have two tables: SQL Fiddle Link

The ScorePerson table contains the identifier of the people and their score from -5 to 15. The end users of my SQL / stored procedure need to create a summary like this:

 Scoreband| TotalNoOfPeople | AvgScore -------------------------------- -5 to 0 | 2 | -2 0 to 5 | 3 | 2 5 to 10 | 2 | 8 10 to 15 | 3 | 13.3 

Range of assessment, i.e. minimum and maximum values ​​must be configured by the end user from the ScoreMinMax table.

I tried to achieve this with the CASE stement, but it generates a column summary. I probably would have to expand it or use UNION from several select statements. But this approach does not seem to be scalable if new rows are added to the ScoreMinMax table. So far, I have managed to achieve some results similar to the example above using CROSS JOINT, but it does not always give the correct results.

Can anyone point me in the right direction how to achieve this?

SQL Fiddle Link

 ScorePerson - contains actual scores 

 ScoreMinMax - the configuration for min and max score bands 

Answer 1

You can use aggregate functions:

 select title ScoreBand, count(*) TotalNoPeople, avg(p.score) AvgScore from scoreperson p inner join scoreminmax m on p.score between m.minscore and m.maxscore group by Title order by cast(left(title, 2) as int) 

see SQL Fiddle with Demo

If you don’t have a person in the existing range, you can give us something like this:

 select case when title is not null then title else 'No Range' end ScoreBand, count(personid) TotalNoPeople, avg(p.score) AvgScore from scoreperson p left join scoreminmax m on p.score between m.minscore and m.maxscore group by id, Title order by id 

see SQL Fiddle with Demo

edit # 2, based on your comments you can use:

 select m.title ScoreBand, count(p.personid) TotalNoPeople, avg(p.score) AvgScore from scoreminmax m left join scoreperson p on p.score between m.minscore and m.maxscore group by m.id, m.Title order by m.id; 

see SQL Fiddle with Demo

Answer 2

Try

 Select Title, count(personid), AVG(score) from scoreminmax left join scoreperson on scoreperson.score>=minscore and scoreperson.score<maxscore group by ID,title order by ID 

Please note that I included estimates at the boundaries of (0.5.10) in only one of the groups.

Answer 3

 select smm.Title Scoreband, count(*) TotalNoOfPeople, avg(sp.Score) AvgScore from ScorePerson sp inner join ScoreMinMax smm on sp.Score >= smm.MinScore and sp.Score < smm.MaxScore group by smm.Title