Query table and group results into ranges in Laravel

I have a database table that stores multiple records of survey scores, the scores are between 1-100. I’m trying to present a frequency distribution on the apps front end, by grouping the scores into the following range;

Less than 20
20-30
30-40
40-50
50-60
60-70
70-80
80-90
90-100

So if the table had the data 87, 92, 95, 98, the user would see

80 – 90 (1)
90 – 100 (3)

etc. I think collections are the way to go about it, but I don’t know where to start to get this sort of output, or whether it’s even possible in Laravel?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Yes, it’s possible. I believe this is the SQL query that you need (assume your table name is “scores”, and “score” is the appropriate field):

select (case when score between 0 and 20 then 'Less than 20'
             when score between 21 and 30 then 'Between 21 and 30'
             when score between 31 and 40 then 'Between 31 and 40'
             when score between 41 and 50 then 'Between 41 and 50'
             when score between 51 and 60 then 'Between 51 and 60'
             when score between 61 and 70 then 'Between 61 and 70'
             when score between 71 and 80 then 'Between 71 and 80'
             when score between 81 and 90 then 'Between 81 and 90'
             when score between 91 and 100 then 'Between 91 and 100'
                 end) as score_range, count(*) as count
from scores
group by score_range
order by min(score);

So for Laravel it could work like this:

$frequency = DB::select("SELECT (CASE
     WHEN score BETWEEN 0 AND 20 THEN 'Less than 20'
     WHEN score BETWEEN 21 AND 30 THEN '20-30'
     WHEN score BETWEEN 31 AND 40 THEN '30-40'
     WHEN score BETWEEN 41 AND 50 THEN '40-50'
     WHEN score BETWEEN 51 AND 60 THEN '50-60'
     WHEN score BETWEEN 61 AND 70 THEN '60-70'
     WHEN score BETWEEN 71 AND 80 THEN '70-80'
     WHEN score BETWEEN 81 AND 90 THEN '80-90'
     WHEN score BETWEEN 91 AND 100 THEN '90-100'
         END) AS score_range, COUNT(*) as count
    FROM scores
    GROUP BY score_range
    ORDER BY MIN(score);");

You can just edit the text titles.

In this query “40-50” (for example) it means, that the score is between 41 and 50. Also you can replace “ORDER BY MIN(score)” to “ORDER BY count” if you want.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating