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

[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 у полей вьюхи

[MironovI]

Цитата:

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

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

Ага, точно, ну тогда это вариант.. если глюки вьюх не беспокоят

[ZVV]

Цитата:

Ага, точно, ну тогда это вариант.. если глюки вьюх не беспокоят

Цитата:

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

Ну да, вариант, при условии, что вьюхи будут создаваться динамически для каждой новой динамической потребности. Ну можно конечно проверять, что похожая вью уже есть и можно её использовать. А если нет, то создавать новую...

Да уж... "Красота"...

[ice]

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

[mazzy]

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

[ice]

Цитата:

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

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

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

[polygris]

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

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

Цитата:

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

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

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

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

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

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

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