SQL count function not working -

SQL count function not working -

i want count number of instances tuition not equal 99999.99, having hard time getting sql count function work. have next query:

select year, college, case when tuition < 99999.99 'other' else to_char(tuition) end tuition, count(*) tuition_count enrolment grouping year, college, tuition order year, college, tuition

i expecting next results, count "other" rolled up.

year college tuition tuition_count --------------------------------------------- 2012 collegea other 123 2012 collegea 99999.99 456

instead, getting multiple instances of "other", 1 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

you need grouping (in grouping statement) want. this:

select year, college, case when tuition < 99999.99 'other' else to_char(tuition) end tuition, count(*) tuition_count enrolment grouping year, college, case when tuition < 99999.99 'other' else to_char(tuition) end order year, college, case when tuition < 99999.99 'other' else to_char(tuition) end

this looks nicer:

select year, college, tuition, count(*) tuition_count ( select year, college, case when tuition < 99999.99 'other' else to_char(tuition) end tuition enrolment ) subselect grouping year, college, tuition order year, college, tuition

sql count