2

I am trying to bucket values within my table by the range they fall in, for example, if my table is the following:

course_name | current enrollment
course_1    | 10
course_2    | 200
course_3    | 500

I get the following result:

enrollment_range | courses
10               | 1
100              | 1
500              | 1

So far, I have the following:

SELECT 
  CASE
    WHEN courses.current_enrollment >= 500 THEN 500
    WHEN courses.current_enrollment >= 250 THEN 250
    WHEN courses.current_enrollment >= 100 THEN 100
    WHEN courses.current_enrollment >= 50 THEN 50
    WHEN courses.current_enrollment >= 30 THEN  30
    WHEN courses.current_enrollment >= 10 THEN 10
  END enrollment_range, count() AS total
FROM courses
GROUP BY enrollment_range
ORDER BY enrollment_range ASC

but I end up with an extra result that is the total number of courses I have, so I get something like the following:

enrollment_range | courses
10               | 1
100              | 1
500              | 1
                 | 3
rdurrani
  • 111
  • 1
  • 6
  • Your query would not produce zero values, so I don't believe that is the query you are really running. – Gordon Linoff Jun 08 '17 at 19:35
  • @GordonLinoff that was a mistake on my part in copying the result over. I made the necessary edits. Thanks for pointing it out! – rdurrani Jun 08 '17 at 19:39
  • There is no way to produce the final result using the sql query you provided. I guess you need to at least have something like: WHEN courses.current_enrollment >= 10 and courses.current_enrollment <30 THEN '10-30' to show the final result. – Tianyun Ling Jun 08 '17 at 19:59
  • @TianyunLing Once again, a mistake on my part. I copied result down wrong. Necessary edits have been made. Thanks! – rdurrani Jun 08 '17 at 20:04

2 Answers2

0

In you sql, you should use a group in the count. In my SQL server, I can produce the correct result using the following script :

SELECT 
  CASE
    WHEN current_enrollment >= 500 THEN 500
    WHEN current_enrollment >= 250 THEN 250
    WHEN current_enrollment >= 100 THEN 100
    WHEN current_enrollment >= 50 THEN 50
    WHEN current_enrollment >= 30 THEN  30
    WHEN current_enrollment >= 10 THEN 10
  END as enrollment_range, t.course_name, t.count
FROM courses 

join 
( select  Count(course_name) as count,course_name FROM courses group by course_name ) t

on courses.course_name = t.course_name
Tianyun Ling
  • 1,045
  • 1
  • 9
  • 24
0

The extra result was the count of courses that did not fall within the specified brackets, in this case, courses with enrollment below 10.

rdurrani
  • 111
  • 1
  • 6