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'
select distinct
FIRST_VALUE(currency) OVER (PARTITION BY currency ORDER BY created_at desc) currency,
FIRST_VALUE(price) OVER (PARTITION BY currency ORDER BY created_at desc) price
from course
SELECT
strftime('%Y', case Dub_date when 'Reg' then reg_date else terminate_date end) AS Date_,
count(case Dub_date when 'Reg' then reg_date end) AS opened_count,
count(case Dub_date when 'Terminate' then terminate_date end) As terminate_count
FROM COMPANIES, (select 'Reg' Dub_date union all select 'Terminate')
group by Date_
having Date_ is not null
select store, bill_number, sum(case product when 'Accessory' then quantity else 0 end) quantity
from table_name
where product in ('Flower', 'Accessory')
group by store, bill_number
having count( distinct product) = 2
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(*) from class c
group by Class_type