Суммы по двум таблицам

[ice]

Всем привет!
Есть задача посчитать суммы в двух связанных(1:n) таблицах сгруппировав их по полю.
такой запрос приводит к неправильному результату, т.е. суммы в первой таблице считаются несколько раз (сколько записей во второй):

while select sum(LineAmount) from Table1 
	group by Table1.ItemId
	join sum(Qty), sum(CostAmount) from Table2 
		where Table1.Id = Table2.Id

как правильно можно посчитать одним запросом?

[ZVV]

По-моему, никак. Делайте вложенными циклами...

[raz]

static void JobTest(Args _args)
{
    InventTrans         InventTrans;
    InventJournalTrans  InventJournalTrans;
    ;
    select sum(Qty), count(RecId) from InventTrans group by TransRefId
    join sum(Qty), sum(CostAmount) from InventJournalTrans
    where InventJournalTrans.InventTransId == InventTrans.InventTransId
       && InventJournalTrans.JournalId     == "СЖ00546541";

    info(strfmt("%1   %2   %3    %4",InventTrans.Qty, InventTrans.RecId, InventJournalTrans.Qty, InventJournalTrans.CostAmount));
}

Результат:
-9 720,00 2 -9 720,00 -57 639,60

[ZVV]

2 raz: Извини, но пример "ни о чём" в контексте имеющегося вопроса.
В приведённом примере связь похоже 1 к 1-му. А нужно 1 ко многим. И группировка должна быть на "таблице-шапке".

Вот пример raz, не работающий на правильных данных и мой вариант решения исходной задачи:

static void ZVVJobTest(Args _args)
{
    InventTrans         InventTrans;
    InventJournalTrans  InventJournalTrans, InventJournalTransIn;
    ;

    // Журнал "УЗЖ001779"
    // В нём одна строка с кол-вом 10 (цена 20, сумма 200)
    // По ней 2 проводки с кол-вом 5 в каждой

    select sum(Qty), count(RecId) from InventTrans group by TransRefId
    join sum(Qty), sum(CostAmount), count(RecId) from InventJournalTrans
    where InventJournalTrans.InventTransId == InventTrans.InventTransId
       && InventJournalTrans.JournalId     == "УЗЖ001779";

    info(strfmt("Неверно! Проводка: %1   %2   Строка: %3    %4   %5",InventTrans.Qty, InventTrans.RecId, InventJournalTrans.Qty, InventJournalTrans.RecId, InventJournalTrans.CostAmount));

    while Select sum(Qty), sum(CostAmount), count(RecId) from InventJournalTrans
        group by ItemId
        where InventJournalTrans.JournalId     == "УЗЖ001779"
    {

        // Второй Джоин строк журнала для демонстрации общего случая, когда нет группируемого поля во второй таблице
        //  (возможно в некотрых случаях по смыслу он и не нужен!)
        select sum(Qty), count(RecId) from InventTrans
           join count(recid) from InventJournalTransIn
            where InventJournalTransIn.InventTransId    == InventTrans.InventTransId
               && InventJournalTransIn.JournalId     == "УЗЖ001779"
               && InventJournalTransIn.ItemId           == InventJournalTrans.ItemId;

        info(strfmt("Верно! Проводка: %1   %2   Строка: %3    %4   %5",InventTrans.Qty, InventTrans.RecId, InventJournalTrans.Qty, InventJournalTrans.RecId, InventJournalTrans.CostAmount));
    }
}

Результат:
Неверно! Проводка: 10,00 2 Строка: 20,00 2 400,00
Верно! Проводка: 10,00 2 Строка: 10,00 1 200,00

[ice]

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

[ZVV]

Ну боюсь, что тем не менее придётся что-то менять в вашей динамике.
В аксапте одним запросом вы такое не сможете изобразить. По крайней мере, мне в голову ничего не приходит.
В оракле ещё можно было бы через аналитические функции выкрутиться, но и то, это получится такой навороченный и нечитаемый селект в вашем случае "(множество группировок, джойнов, фильтров) "...

[vallys]

Цитата:

Сообщение от ice

Всем привет!
... как правильно можно посчитать одним запросом?

Если в аксапте, то только через вьюху со своими плюсами и минусами...

[ice]

Цитата:

Сообщение от vallys

Если в аксапте, то только через вьюху со своими плюсами и минусами...

вьюха не поддерживает более одного джойна к таблице

[vallys]

Цитата:

Сообщение от ice

вьюха не поддерживает более одного джойна к таблице

Не понял.
Я имел в виду что запрос вида

select ... 
from Table1, (select id, ... from Table2 group by id) as Table2
where Table1.id = Tablt2.id
[group by ...]

в аксапте одним запросом можно реализовать только с помощью вьюхи, которая будет представлять собой подзапрос с группировкой.
Вот только на практике зачастую это лишнее...

[MironovI]

Цитата:

Сообщение от vallys

в аксапте одним запросом можно реализовать только с помощью вьюхи, которая будет представлять собой подзапрос с группировкой.
Вот только на практике зачастую это лишнее...

В стандартных вьюхах нет параметра OrderMode насколько я помню

[vallys]

Цитата:

Сообщение от MironovI

В стандартных вьюхах нет параметра OrderMode насколько я помню

За-то есть свойство Aggregation у полей вьюхи

[ice]

Всем большое спасибо за участие!
Проблему решил с использованием временной таблицы

[mazzy]

зачем временная таблица? лучше используйте класс KeySum

[ice]

Цитата:

Сообщение от mazzy

зачем временная таблица? лучше используйте класс KeySum

отчет очень сложный, и временная таблица уже использовалась для хранения собранных данных, просто добавил в ней количество записей

[polygris]

а что собственно делает класс KeySum?

[Владимир Максимов]

Цитата:

Сообщение от polygris

а что собственно делает класс KeySum?

Сохраняет и модифицирует набор чисел, позволяя обращаться к каждому из них по "ключу". Т.е. не просто по номеру (как в массиве), но по некоему идентификатору. Как правило, символьному, но тип идентификатора может быть любым. В частности, и числом.

При этом, имеется возможность (при желании) упорядочивать элементы набора по значению ключа, а также подсчитывать итоговую сумму всех числовых данных.

Также можно организовать хранение "многомерных" чисел. Т.е. каждое "число" может представляться контейнером из набора чисел.

Основной метод inventSum.updateNow() прибавляет к указанному элементу указанное значение.

Используется для хранения промежуточных результатов расчета и "накопительных" значений. В смысле суммы, получающейся в результате прогона некоего цикла, где сумма увеличивается на значение, вычисляемое в теле цикла.