CODEWITHSUNDEEP
sqlcount
b00kgrrl
b00kgrrl

Reputation: 597

SQL count function not working

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

Upvotes: 0

Views: 447

Answers (1)

Hogan
Hogan

Reputation: 70523

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

Upvotes: 1

Related Questions