SQLize | PHPize | SQLtest

with cte_upd_currency as ( select c1.id , c1.updated , c2.name , c2.rate_to_usd from (select id , max(updated) as updated from currency group by id) as c1 join currency as c2 on c2.id = c1.id and c2.updated = c1.updated ) select coalesce(u.name,'not defined') as name , coalesce(u.lastname,'not defined') as lastname , b.type as type , sum(b.money) as volume , coalesce(cte_uc.name,'not defined') as currency_name , coalesce(cte_uc.rate_to_usd, 1) as last_rate_to_usd , sum(b.money) * coalesce(cte_uc.rate_to_usd, 1) as total_volume_in_usd from balance as b full join public.user as u on u.id = b.user_id left join cte_upd_currency as cte_uc on cte_uc.id = b.currency_id group by u.id, b.type, cte_uc.name, cte_uc.rate_to_usd order by name desc, lastname, type ;