есть таблица, нужно поделить колонку total_income на колонку num_payments и вывести в колонку average_income для ордеринга по ней, как это записать
select - cs as customer, - sum(case when stat = 'pc' then value else 0 end) as num_payments, - sum(case when stat = 'ps' then value else 0 end) as total_income, - month from - Stats group by - customer, - month order by - average_income
Аглая Шниц
Искусственный Интеллект
(137849)
хм, а если с подзапросом?
update Stats set
average_income = a.avg_income
from Stats s
join (
select
cs, month,
sum(case when stat = 'ps' then value end)/sum(case when stat = 'pc' then value end) as avg_income
from Stats
group by cs, month
) a on Stats.cs = a.cs and Stats.month = a.month
select
- cs as customer,
- sum(case when stat = 'pc' then value else 0 end) as num_payments,
- sum(case when stat = 'ps' then value else 0 end) as total_income,
- month
from
- Stats
group by
- customer,
- month
order by
- average_income