Сумма по полю для текущей записи

[Harry]

Имеется: 2 таблицы People и Money связь по полю Sum как 1 ко многим (одному человеку принадлежит несколько выплат)
Нужно: Посчитать сумму по полю Sum таблицы Money для конкретного человека.
Запрос SELECT Sum(Money.Sum) FROM Money WHERE.....  какое написать условие? Если задать условие WHERE Money.PeopleCode=People.PeopleCode все равно получится сумма по ВСЕМ записям, а не по текущей. А как правильно?

[Alf]

Если мы хотим подсчитать все выплаты человека с кодом PeopleCode=N, то достаточно задать условие WHERE Money.PeopleCode=N, в данном примере вроде достаточно ограничиться одной таблицей Money.

[Sla]

SELECT Sum(Money.Sum) FROM Money WHERE...
group by PeopleCode

[Harry]

Если мы хотим подсчитать все выплаты человека с кодом PeopleCode=N, то достаточно задать условие WHERE Money.PeopleCode=N, в данном примере вроде достаточно ограничиться одной таблицей Money.

Это если бы известен был конкретный код. Идея такая - мы листаем справочник People и заходя в карточку любого клиента видим сумму выплат именно по нему. Но как реализовать такой запрос, типа конструкции WHERE People.PeopleCode=Money.CurrentRecord.PeopleCode ? Ведь понятия текущей записи нет в запросах? Или есть?

[Harry]

SELECT Sum(Money.Sum) FROM Money WHERE...
group by PeopleCode

Тогда я получу суммы ВСЕХ плательщиков, сгруппированные по коду. А мне нужен один, как бы фильтр по коду...

[Sla]

Т.е. ты хочешь получить что-то типа:

Код:

peopleCode1  Sum1
                    Sum2
                    Sum3
peopleCode2  Sum1
                    Sum2
                    Sum3

?

[Sla]

Если это так, то надо раскручивать курсор по PeopleCode
выбираешь пиплов, а потом для каждого пипла выбираешь выплаты

[Alf]

Это если бы известен был конкретный код. Идея такая - мы листаем справочник People и заходя в карточку любого клиента видим сумму выплат именно по нему. Но как реализовать такой запрос, типа конструкции WHERE People.PeopleCode=Money.CurrentRecord.PeopleCode ?

А можно тогда поглядеть пример, как бы это должно было выглядеть? Что имеем на входе и что хотим получить в результате?

Ведь понятия текущей записи нет в запросах? Или есть?

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

[Sla]

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

[Falsehood]

попробовала в FoxPro 6.0

Код:

select PeopleCode, sum(Sum) from Money where Money.PeopleCode = People.PeopleCode into cursor Qqq

выбирается одна строчка с суммой выплат по тому человеку, на котором в данный момент стоит курсор в таблице People. или требуется что-то другое?

[Harry]

Расшифрую задачу.
Есть организация, члены которой платят членские взносы. Список членов - в виде таблицы People.
Взносы могут платиться нерегулярно, кем то больше, кем то меньше, кто-то их может совсем не заплатить.
Т.е. хочется, идя курсором по табличке списка людей зайти в форму конкретного человека, в которой на одной из вкладок тоже есть табличка - именно ЕГО выплат. А отдельно - сумма этих выплат.
Выплаты сделаны отдельной таблицей Money, подчиненной таблице People.
Так вот, как раз реализовано все, кроме итоговой суммы выплат по человеку.

P.S. Программа пишется в Delphi, но, думаю, корректней этот вопрос все же задавать в форуме БД Сами таблицы - Парадоксовские.

[Alf]

Все же непонятно, в какой именно момент производится расчет взносов.

1. Заранее строится таблица вида "Человек - Сумма", и при проходе по списку людей оттуда берутся данные.

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

Какой из вариантов лучше подходит?

[Sla]

SELECT PeopleCode КодПипла from People WHERE
все пиплы

SELECT Money.Sum FROM Money WHERE Money.PeopleCode= :КодПипла
все взносы пипла

SELECT Sum(Money.Sum) FROM Money WHERE Money.PeopleCode= :КодПипла

Это и будет вся сумма по пиплу, КодПипла ты знаешь

В чем трудность?

[Harry]

SELECT PeopleCode КодПипла from People WHERE
все пиплы

SELECT Money.Sum FROM Money WHERE Money.PeopleCode= :КодПипла
все взносы пипла

SELECT Sum(Money.Sum) FROM Money WHERE Money.PeopleCode= :КодПипла

Это и будет вся сумма по пиплу, КодПипла ты знаешь

В чем трудность?

Не знаю я код. Мне нужно выбрать взносы по текущему пиплу, в карточку которого я вошел. Т.е. привязать запрос к конкретному коду нельзя.

[Harry]

Все же непонятно, в какой именно момент производится расчет взносов.

1. Заранее строится таблица вида "Человек - Сумма", и при проходе по списку людей оттуда берутся данные.

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

Какой из вариантов лучше подходит?

Первый вариант.

[Югг]

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

SELECT Money.Sum FROM Money WHERE Money.PeopleCode= :КодПипла
все взносы пипла

и все.

[Alf]

Первый вариант.

Тогда вполне корректно

Код:

SELECT PeopleCode, SUM(Sum)
FROM Money
GROUP BY PeopleCode

Этот запрос вернет таблицу вида "КодПерсоны - СуммаПлатежей", которой можно впоследствии воспользоваться для отображения.

Единственное замечание - называть столбцы таблицы именами встроенных функций и зарезервированными словами явно не лучшее решение (это я по поводу столбца Money.Sum). Лучше переименовать во что-то нейтральное типа Payment etc.

[HandKot]

а если попробовать так, используюя такой запрос:

Код:

SELECT
  People.*
  Vznosy.SummaVznosov
FROM
  People
INNER JOIN
  (SELECT 
      PeopleCode, 
      SUM(Money.Sum) AS [SummaVznosov] 
   FROM
      Money
   GROUP BY 
     PeopleCode) AS Vznosy ON Vznosy.PeopleCode = People.PeopleCode

в парадоксе не силен, но INNER JOIN, вроде относится к стандарту SQL и должно все отработать

[Harry]

Тогда вполне корректно

Код:

SELECT PeopleCode, SUM(Sum)
FROM Money
GROUP BY PeopleCode

Этот запрос вернет таблицу вида "КодПерсоны - СуммаПлатежей", которой можно впоследствии воспользоваться для отображения.

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

HandKot: идею понял, но что-то не работает. Попытаюсь еще помучать код, не может быть, чтобы Парадокс не понимал вложенных запросов...

[Alf]

Все же непонятно, в какой именно момент производится расчет взносов.

1. Заранее строится таблица вида "Человек - Сумма", и при проходе по списку людей оттуда берутся данные.

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

Какой из вариантов лучше подходит?

Первый вариант.

Тогда вполне корректно

Код:

SELECT PeopleCode, SUM(Sum)
FROM Money
GROUP BY PeopleCode

Этот запрос вернет таблицу вида "КодПерсоны - СуммаПлатежей", которой можно впоследствии воспользоваться для отображения.

Вот именно, таблицу. А нужно единственное значение для текущего пипла.

Или я чего-то не понимаю, или...

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

[Falsehood]

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

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

Код:

select PeopleCode, sum(Sum) from Money where Money.PeopleCode = People.PeopleCode group by PeopleCode into cursor Qqq

или предварительно запомни его в переменную :

Код:

lcPeopleCodу = People.PeopleCode
select PeopleCode, sum(Sum) from Money where Money.PeopleCode = lcPeopleCode group by PeopleCode into cursor Qqq

[Sla]

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

Код:

select PeopleCode, sum(Sum) from Money where Money.PeopleCode = People.PeopleCode group by PeopleCode into cursor Qqq

этот запрос работать не будет
А запомнить код пипла я уже предлагал
SELECT PeopleCode КодПипла from People WHERE ...

[Falsehood]

Sla, почему не будет работать? в FoxPro 6.0 работает, а в Парадоксе не сработет?

[Sla]

потому что fox это не SQL, в Парадоксе может и сработает
Oracle? MsSql? MySql? работать не будет

[HandKot]

Harry 
может тебе подойдет такой вариант (хотя мож он и проскакивал уже в теме)

Код:

SELECT
  People.*,
  (SELECT SUM(Sum) FROM Money WHERE Money.PeopleCode = People.PeopleCode) AS [Взносы]
FROM 
  People

[Harry]

Или я чего-то не понимаю, или...

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

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

[Sla]

В форме лежит таблица взносов уже по этому, конкретному человеку. И сумма по нему же. Вот сумма-то и не получается...

чтобы получить таблицу взносов конкретного пипла, тебе нужно знать код этого пипла, а зная код ты получишь сумму.
или я чего-то не понимаю

[Harry]

Да, но код я и не знаю. Я ведь не могу знать, какого пипла я буду выбирать. Так что код жестко привязывать не могу. Вариантов, похоже, тут два. Один - повесить на кнопку входа в форму запрос на получение кода, второй - последний вариант, который предложил HandKot. В той вариации, что он написал, запрос дает ПЕРВУЮ запись. Но уже по конкретному пиплу. Истина где-то рядом, сижу, ищу...

[Sla]

Погодь!
У тебя две формы.
Первая - это пиплы
Вторая - это взносы конкретного пипла.
Как эти две формы связаны друг с другом?
запрос HandKot даст суммы взносов каждого пипла

[Alf]

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

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

Здесь возможны два решения.

Первое - динамически формировать строку запроса SQL во время выполнения программы, поскольку это - всего лишь текстовая строка, и она не компилируется с программой, а интерпретируется сервером баз данных непосредственно во время выполнения. Так, если код текущей персоны CurrPersonId, то формируется строка вида:

Код:

strSQL = "SELECT SUM(Sum) FROM Money WHERE (PersonId = " + CurrPersonId.ToString() + ")"

а потом этот запрос выполняется.

Второе решение - использовать параметризованный запрос, который ведет себя подобно процедуре с формальными параметрами. Тогда во время выполнения запроса нужно сообщить значение его фактического аргумента. В статьях по ADO.NET я приводил пример, как это сделать на данной платформе. На других платформах есть аналогичные средства.

[Harry]

ОРГАНИЗАЦИЯ БД: две таблицы - People и Vznosi. Связь по полю PeopleCode как один ко многим (один человек-много выплат)
ИНТЕРФЕЙС: основная форма - представлена в виде таблицы пиплов, переходим на любого, клацаем кнопку Форма, заходим в форму конкретного пипла. В этой форме таблица выплат этого конкретного пипла и общая сумма по пиплу. Запрос в виде, который дал HandKot приводит к ошибке, говорит, что нет поля Sum(Summa). Неясно, глюк это парадокса или просто нужно писать по другому. По идее, высчитываемое поле должно отображаться в списке полей....

[Harry]

SELECT People.*,
(SELECT Sum(Vznosi."Summa")
FROM "Vznosi.DB" Vznosi
WHERE Vznosi."PeopleCode"=People."PeopleCode")
FROM "People.DB" People

Вот этот запрос. Он выдает ошибку, говорит не найдено поле Sum(Summa). Действительно, в выпадающем списке доступных полей его нет. Т.е. второй Select не выполняется.

[HandKot]

Harry, вылож на форум структуры таблиц, всеж легче будет

[HandKot]

тут появилась еще одна идея - использовать св-во Filter объекта Recordset, либо объекта таблица

(делфи не знаю, пишу на басике)

На главной форме :
dim rsPeople as ADODB.Recordset

rsPeople.Open "SELECT * FROM People", connection
set dbgrdPeople.DataSource = rsPeople    ' присваиваем таблице источник строк
BoundColumn = rs!PeopleCode

На форме взносов:
dim rsVznosy as ADODB.Recordset
dim rsTotalVznosy as ADODB.Recordset

rsVznosy.Open "SELECT * FROM Vznosy", connection
rsTotalVznosy.Open "SELECT PeopleCode, SUM(Summa) AS [TotalSum] FROM Vznosy GROUP BY PeopleCode"
set dbgrdVznosy = rsVznosy
set txtTotal.Text = rsTotalVznosy!TotalSum

при открытий формы взносов устанавливаем фильтр
sub frmVznosyLoad()
  rsVznosy.Filter = "PeopleCode = " & frmPeople.dbgrdPeople.BoundColumn
  rsTotalVznosy.Filter = "PeopleCode = " & frmPeople.dbgrdPeople.BoundColumn
 
end sub

в Дельфи может отличатся, но идею вроде нормально объяснил

[Harry]

Структура - см. выше. Главная таблица - пиплы, подчиненная - выплаты, связанная с главной по коду пипла.
Что касается идеи - попробую сваять что-либо подобное, если по другому не получится. Просто странно, что такую простую проблему не получается решить обычным запросом. Ну не может же в Делфях INNER JOIN не работать..... что-то я пишу неправильно. Я тут уже ссылку дал, на форуме по Delphi, может чего подскажут....

[Sla]

Ну не может же в Делфях INNER JOIN не работать..... что-то я пишу неправильно.

а причем здесь inner join и дельфи, не путай праведное с грешным
и где в твоей задаче нужен  inner join?

[Harry]

а причем здесь inner join и дельфи, не путай праведное с грешным
и где в твоей задаче нужен inner join?

Да ладно, не придирайся Понятно, что имеется в виду организация данных в Парадокс.
А INNER JOIN я использовал бы для подзапросов. Увы, как я только что вычитал в мануале, в Local SQL (Парадокс в частности) вложенные подзапросы не поддерживает....

[Sla]

а разве так нельзя?

Код:

SELECT People.PeopleCode, Sum(Vznosi."Summa")
FROM "Vznosi.DB" Vznos, "People.DB" People
WHERE Vznosi."PeopleCode"=People."PeopleCode"
Group by People.PeopleCode

в принципе это и есть inner join

[HandKot]

Sla  согласен, но в данном запросе не будет доп.информации по пиплам, а она может нужна, либо надо в GROUP BY добавлять и остальные поля, которые нужно выбрать

[Sla]

да конечно, но код приведен как пример

[HandKot]

Harry в таком случае остается сразу выбрать все данные (пиплы, выплаты) в рекордсеты, настроить таблицы с источниками на эти рекордсеты и, к примеру,
на событие "Изменение строки" таблицы "пиплов" устанавливать фильтр на рекордсете "выплат" (осталось узнать, если такое св-во в рекордсете в дельфи)

(у меня был такой проект для синхронного отображения данных в двух таблицах)

[Harry]

Harry в таком случае остается сразу выбрать все данные (пиплы, выплаты) в рекордсеты, настроить таблицы с источниками на эти рекордсеты и, к примеру,
на событие "Изменение строки" таблицы "пиплов" устанавливать фильтр на рекордсете "выплат" (осталось узнать, если такое св-во в рекордсете в дельфи)

(у меня был такой проект для синхронного отображения данных в двух таблицах)

Так и придется делать. Я соорудил следующий запрос:

Код:

SELECT People."PeopleCode", (Sum(Vznosi."Summa")) AS Total
FROM "People.DB" People, "Vznosi.DB" Vznosi
WHERE Vznosi."PeopleCode"=People."PeopleCode"
GROUP BY People."PeopleCode"
ORDER BY Total

Но он выбирает сумму по текущей записи, теперь придется учесть перемещение курсора по записям, чтобы динамически менялись суммы.

[HandKot]

Harry, я имелл ввиду следующее:
главная форма
rsP = "select * from people"

это источник строк грида

подчиненная форма (или грид)
rsV = "select peoplecode, sum(Summa) from Vznozy group by peoplecode"
rsV.Filter = "PeopleCode = " & rsP!PeopleCode
источник строк для грида

на событие смены строки в гриде
sub грид_People_RowChange()
rsV.Filter = "PeopleCode = " & rsP!PeopleCode
end sub

примерно так


PS: хотя не мне учить, разработчик сам решает как лучше сделать