SQL count function not working

Question

I want to count the number of instances where tuition does not equal 99999.99, but I am having a hard time getting the SQL count function to work. I have the following query:

select  
    year, college,
    case
        when tuition < 99999.99 then 'Other'
        else to_char(tuition)
    end tuition, 
    count(*) as tuition_count
from
    enrolment
group by    
    year, college, tuition
order by    
    year, college, tuition

I was expecting the following results, with the count for "Other" rolled up.

YEAR    COLLEGE     TUITION     TUITION_COUNT
---------------------------------------------
2012    CollegeA    Other       123
2012    CollegeA    99999.99    456

Instead, I am getting multiple instances of "Other", one for each distinct value of tuition.

YEAR    COLLEGE     TUITION     TUITION_COUNT
---------------------------------------------
2012    CollegeA    Other       100
2012    CollegeA    Other       20
2012    CollegeA    Other       3
2012    CollegeA    99999.99    456

Hogan · Accepted Answer · 2014-10-16 19:51:02Z

You need to group by (in the group by statement) what you want. Like this:

select  year, college,  case
                          when tuition < 99999.99 then 'Other'
                          else to_char(tuition)
                        end as tuition, count(*) as tuition_count
from enrolment
group by year, college, case
                          when tuition < 99999.99 then 'Other'
                          else to_char(tuition)
                        end
order by year, college, case
                          when tuition < 99999.99 then 'Other'
                          else to_char(tuition)
                        end

This looks nicer:

select  year, college, tuition, count(*) as tuition_count
from 
(
  select  year, college,  case
                            when tuition < 99999.99 then 'Other'
                            else to_char(tuition)
                          end as tuition
  from enrolment
) subselect
group by year, college, tuition
order by year, college, tuition

Collectives™ on Stack Overflow

SQL count function not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related