Сумма из двух таблиц, не получается, прошу помощи

sukhof

Добрый день! Есть две таблицы. Первая называется ca_predmet- предметы договоров, где id_ca - ид самого договора
ca_predmet_id
делаю вот такой запрос
select id_ca, sum(ca_summa) from ca_predmet group by id_ca
получаю результат, сколько общая сумма по договору.Есть другая таблица plan_commit - исполнение договора, где id_plan_src- источник из которого оплатили
id_ca_predmet
делаю вот такой запрос и вижу сколько было оплачено из разных источников по предметам договора:
select id_ca_predmet, sum(commit_sum) from plan_commit group by id_ca_predmet
все это работает, все хорошо. Но стоит задача, надо видеть сколько осталось по договору не оплаченной суммы, т.е. например из того что есть мы видим что по 5 предмету сумма оплачена полностью это 34700, по 7 предмету оплачена только 1000 и остаток по данному предмету составляет 10000, а остаток по 6 предмету 23000 в сумме этот остаток по договору получается 33000 - вот именно этот остаток по договору в целом и надо вывести. делаю вот так:
select ca_id, sum(ca_summa), sum(commit_sum), sum(ca_summa)-sum(commit_sum) as summa from plan_commit
left join ca on ca_id=plan_commit.id_ca
left join ca_predmet on ca_predmet_id=plan_commit.id_ca_predmet
group by plan_commit.id_ca
в результате я должен увидеть сколько по данному договору всего сумма ( sum(ca_summa) ), сколько уже оплачено ( sum(commit_sum) ), и сколько осталось денег по договору еще не оплаченных ( sum(ca_summa)-sum(commit_sum) )но вот ничего не работает, ибо выводит какой то такой гонимый остаток - sum(commit_sum) - 44700 (выводит верно), sum(ca_summa)-184500, a sum(ca_summa)-sum(commit_sum) - 139800 . Сижу бьюсь со вчерашнего вечера запросы всяко крутил, уже крыша едет. пожалуйста помогите разобраться. Насколько я понимаю где то в группировке я ошибся.
6 ответов

sukhof

sukhof,ca - таблица договоров? перепишите запрос с полными названиями полей(таблица.поле), а то у вас то id_ca, то ca_id, да ещё не пойми из какой таблицы... или хотя бы выложите DDL всех трёх таблиц.а ещё групбай у вас не по ведущему полю.


sukhof

sukhof, джойнить нужно не сами таблицы, а результаты их группировок.:
Select *
 from (select id_ca, sum(ca_summa) from ca_predmet group by id_ca) t1
 [left] join (select id_ca_predmet, sum(commit_sum) from plan_commit group by id_ca_predmet) t2
 on ...


sukhof

Спасибо за помощь! Получилось сделав вот так:
select t1.id_ca, t1.ca_summa, t2.commit_sum, t1.ca_summa-t2.commit_sum
 from (select id_ca, sum(ca_summa) as ca_summa from ca_predmet group by id_ca) t1
 left join (select id_ca, sum(commit_sum) as commit_sum from plan_commit group by id_ca) t2
 on t1.id_ca=t2.id_ca
Однако смущает чем то меня такой вариант.
ca - таблица договоров? перепишите запрос с полными названиями полей(таблица.поле), а то у вас то id_ca, то ca_id, да ещё не пойми из какой таблицы...
да ca - это таблица договоров. поправил запрос:
select plan_commit.id_ca, sum(ca_summa), sum(commit_sum), sum(ca_summa)-sum(commit_sum) as summa from plan_commit
left join ca on ca.ca_id=plan_commit.id_ca
left join ca_predmet on ca_predmet.id_ca=plan_commit.id_ca
group by plan_commit.id_ca
- результат стал более не понятным
id_ca
вообще ничего понять не могу :(


sukhof

Однако смущает чем то меня такой вариант.
Чем смущает? Подход-то вполне себе стандартный - вложенный запрос, derived table, inline view. Называют по разному, но суть одна - сделал некие предвычисления и на следующем уровне вложенности запроса воспользовался результатом этих вычислений...


sukhof

поправил запрос:
select plan_commit.id_ca, sum(ca_summa), sum(commit_sum), sum(ca_summa)-sum(commit_sum) as summa from plan_commit
left join ca on ca.ca_id=plan_commit.id_ca
left join ca_predmet on ca_predmet.id_ca=plan_commit.id_ca
group by plan_commit.id_ca
- результат стал более не понятным
а вы без суммирования выведите - и сразу всё поймёте :)PS. В качестве лёгкого извращения можно вместо sum(commit_sum) написать avg(commit_sum), в принципе при взаимнооднозначном соответствии plan_commit.id_ca<-->plan_commit.commit_sum этот вариант должен давать верный результат.


sukhof

я выводил без sum и один черт, не пойму почему он выводит один большой список. Не пойму не потому что не знаю, а потмоу что время поджимает и уже голова кипит от ночных работ. вот вижу, есть понимание, но почему - не знаю :(