Grouping with a selection?

Question

SQL

Let's say we have a request like this:

select case when id in (1,2,3,4) then 'Начальная школа'
            when id in (5,6,7,8,9) then 'Средняя школа'
            when id in (10,11) then 'Старшая школа'
           end Class_type,
 COUNT(*), sum(case when s.ocenka>4 then 1 end as) 'Отличники' from class c
group by Class_type

It is necessary that in this type of report, with COUNT (*) 5, the class goes to elementary school, and with sum as it is .....
this is an example, I have a more complex query.

Answer 1

COUNT(*)
Do a subquery instead .

SELECT
  case when id in (1,2,3,4) then 'Начальная школа'
          when id in (5,6,7,8,9) then 'Средняя школа'
          when id in (10,11) then 'Старшая школа'
          end AS Class_type,
  (
    SELECT COUNT(*) 
    FROM class cc 
    WHERE (
      case when сс.id in (1,2,3,4,5) then 'Начальная школа'
      when cc.id in (6,7,8,9) then 'Средняя школа'
      when cc.id in (10,11) then 'Старшая школа'
    ) = (
      case when c.id in (1,2,3,4) then 'Начальная школа'
      when c.id in (5,6,7,8,9) then 'Средняя школа'
      when c.id in (10,11) then 'Старшая школа'
    )
  ), 
  SUM(case when s.ocenka>4 then 1 end) AS 'Отличники' 
FROM class c
GROUP BY Class_type

Something like that through the butt. Confused. Didn't check, no firebird at hand

Answer 2

It is not clear from the description who was on whom =)

select Class_type, 
count(1) over (partition by case when Class_type = 'HZ' then 'Начальная школа' else Class_type end) cnt,
sum(for_sum) over  (partition by case when Class_type = 'HZ' then 'Средняя школа' else Class_type end) sum_
from (
select case when id in (1,2,3,4) then 'Начальная школа'
when id = 5 then 'HZ'
when id in (6,7,8,9) then 'Средняя школа'
when id in (10,11) then 'Старшая школа'
end Class_type, 
case when s.ocenka>4 then 1 else 0 end for_sum
from class c ) t
group by Class_type
having Class_type <> 'HZ'

If you want something more intelligible, give an example of data and the result you would like to get (it will be clearer)