SUM -> NULL

← →
PKT (2002-12-26 04:59) [0]

Запрос:
select SUM(CREDIT) - SUM(DEBET) from ...
Если у всех записей, например, поле DEBET пустое,
то на выходе вместо суммы CREDIT минус нуль возвращается
пустое значение.

Сейчас делаю отдельно запрос SUM(CREDIT), отдельно
SUM(DEBET), проверяю их на if not NULL и нахожу разницу.

А можно ли все-таки одним запросом ?

← →
ЮЮ © (2002-12-26 06:57) [1]

Низзя

← →
REP © (2002-12-26 07:16) [2]

Замени в поле DEBET все значения с NULL на 0. В SQL, если попадается значение NULL в поле, то выражение тоже с ним возвращает NULL. NULL это не 0.

← →
PKT (2002-12-26 07:58) [3]

to: ЮЮ © (26.12.02 06:57)
Спасибо за однозначный ответ, ну тогда оставлю как есть.

← →
-=Sergeante=- © (2002-12-26 09:30) [4]

Чесно говоря, мне тоже не понятна логика такого поведения...
Если из числа вычитаем "пусто", то очевидно и остаться какое-то число, но никак не "пусто".
Сколько раз уже натыкался на эту фичу, столько же раз недоумевал от такого поведения. А оборачивается это лишним кодингом проверок и сравниваний с NULL значениями и заменой оных на 0.
Бред. Если это не бред, то ткните меня носом в чём я не прав.

← →
Alexandr © (2002-12-26 09:33) [5]

null это не ноль. это НЕИЗВЕСТНО.
пример
2+х.з. сколько
чему равно? х.з. сколько.

В чем проблема?
Сначала навтыкаете null куда надо и ненадо, а потом думаете, что у вас null неправильно работает.
Вы его просто готовить не умеете. А коли не умеете, то и используйте лучше. Пока не поймете в нем необходимость.

← →
neXt © (2002-12-26 09:39) [6]

а в MSSQL можно:
select isnull(SUM(CREDIT),0) - isnull(SUM(DEBET),0) from ...

← →
Johnmen © (2002-12-26 09:44) [7]

>-=Sergeante=- © (26.12.02 09:30)

Ты неправ ! Сушествует четкая и одназначная логика работы со значениями полей, в т.ч. и с NULL ("неопределенность"). см. Alexandr © (26.12.02 09:33).
От себя добавлю, что если поле предполагает над собой какие-то действия, то и значение его должно быть определено. А если ты его не определил - наличие провалов при проектировании БД.

← →
Anatoly Podgoretsky © (2002-12-26 09:50) [8]

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

← →
Sergey13 © (2002-12-26 09:50) [9]

2-=Sergeante=- © (26.12.02 09:30)
>Если из числа вычитаем "пусто"...
NULL - это не "пусто", это именно х.з. сколько. 8-) И этим надо уметь пользоваться при проектировании базы. Например это незаменимо во всякого рода ID-шниках, где установить 0 (нуль) нельзя по правилам ссылочной целостности. А вот для вещественных атрибутов (цены, количества и т.д) нужно выставлять дефолтные значения для полей (0 например), тогда и проблем не будет. И производительность вырастет (часто).

← →
myor © (2002-12-26 10:24) [10]

нечего удивляться.
null<>0 и точка
null- это именно неопределенные данные (см. теорию рбд), и не нужно их использовать там, где можно (и нужно) использовать пустые данные, т. е. 0 для числовых полей и "" (пустую строку)- для строк.

← →
-=Sergeante=- © (2002-12-26 10:39) [11]

Ну запинали. Тем не менее, что-то более-менее прояснилось. Вытираю сопли и иду читать теорию. Спасибо.

← →
diMAN © (2002-12-26 10:53) [12]

Вот, лови, я тоже как то столкнулся с этой проблемой и вывернулся следующим образом:

select SUM(CAST(""0""||CAST(CREDIT AS CHAR(30)) AS MONEY)) - SUM(CAST(""0""||CAST(DEBET AS CHAR(30)) AS MONEY)) from ...

Работает 100%. Почему именно так, думаю объяснять не стоит, разберёшся :))

← →
Anatoly Podgoretsky © (2002-12-26 10:54) [13]

-=Sergeante=- © (26.12.02 10:39)
По теории, может это поможет NULL <> NULL

← →
Johnmen © (2002-12-26 11:23) [14]

>diMAN © (26.12.02 10:53)
>Работает 100%.

Где работает ?

← →
Val © (2002-12-26 11:29) [15]

>Johnmen © (26.12.02 11:23)
Где работает ?
подозреваю, что нигде.

← →
myor © (2002-12-26 11:33) [16]

а че это вы тут делаете, а?
автор вопроса уже давно упал с этой ветки!

← →
Johnmen © (2002-12-26 11:38) [17]

>Val © (26.12.02 11:29)
>подозреваю, что нигде.

Вот и у меня закрались подозрения...:)))

← →
MsGuns © (2002-12-26 11:48) [18]

В Local SQL, я думаю, работать будет.

К вопросу же о NULL. Уважая мастаков, не могу не отметить, что порою они бывают излишне категоричны. Что значит фраза

>Alexandr © (26.12.02 09:33)
Сначала навтыкаете null куда надо и ненадо, а потом думаете, что у вас null неправильно работает.
Вы его просто готовить не умеете. А коли не умеете, то и используйте лучше. Пока не поймете в нем необходимость. ?

А если база уже есть, создана n лет назад, содержит полмиллиона записей и успешно юзается m-м кол-вом персонала ?
Искусство программирования заключается не только в том, как проектировать НОВЫЕ проекты, но и как выкручиваться в старых, не так ли ?

← →
Anatoly Podgoretsky © (2002-12-26 11:48) [19]

Да нужен он нам, нам и без него хорошо :-)

← →
Lady D © (2002-12-26 13:28) [20]

SELECT SUM(ALL CREDIT) - SUM(ALL DEBET) from ...
Подсчитает все NOT NULL

← →
-=Sergeante=- © (2002-12-26 15:03) [21]

> MsGuns © (26.12.02 11:48)
> А если база уже есть, создана n лет назад, содержит полмиллиона
> записей и успешно юзается m-м кол-вом персонала ?
> Искусство программирования заключается не только в том,
> как проектировать НОВЫЕ проекты, но и как выкручиваться
> в старых, не так ли ?

О! Здравая мысль.

← →
petr_v_a © (2002-12-26 16:44) [22]

Искусство программирования - в том, чтоб не влезть в дурацкий проект :))
Честно, ничей конкретно не имел в виду :))

← →
MsGuns © (2002-12-26 19:40) [23]

>petr_v_a © (26.12.02 16:44)
> Искусство программирования - в том, чтоб не влезть в дурацкий проект :))

Это искусство называется "дипломатией". У настоящих программеров, как правило, начисто отсутствует 8))

← →
Lady D © (2002-12-27 09:45) [24]

Похоже опять никто не понял (или сделал вид), что я дала "короткое" и верное решение. Lady D © (26.12.02 13:28).
Включение ключей в аггрегатные функции может избавить от многих головных болей. Например, мне нужно выдать отчет по клиентам с суммой продаж и вычислением Profit по каждому. Естественно, должен быть и итог с такими же полями. Нет проблем если каждый из клиентов покупал в этот отрезок времени, а если нет? Его показатели пусты, а COUNT его посчитает. Как же быть с итогом? Очень просто: COUNT(ALL saldo) подсчитает всех, у кого сумма NOT NULL... COUNT(DISTINCT saldo) подсчитает все уникальные saldo(не знаю зачем это здесь, но тем не менее). То же самое справедливо и для SUM,AVG... Проблема в том, что написала об этом Lady и ни у кого не хватит духа сказать: да, ты права, я об этом читал, но давно забыл. Напротив, каждый здесь ждет случая (подходящего и не очень) чтобы попестовать меня. Итак, решение:
SELECT SUM( ALL credit) - SUM( ALL debit) FROM ...
Есть сегодня желающие поспорить?

← →
ЮЮ © (2002-12-27 09:54) [25]

О чём? Подчитать то подсчитает, но если в одном стодбце нет ни одной записи со значением не NULL, то и SUMM() вернет NULL и пезультат будет NULL независимо от суммы по второму столбцу.
а SELECT SUM(ALL credit) - SUM(ALL debit) FROM ... ТОЖДЕСТВЕНЕН авторскому select SUM(CREDIT) - SUM(DEBET) from ...

← →
passm © (2002-12-27 10:00) [26]

ЮЮ © (27.12.02 09:54)> Верно сказано насчет тождественности.
.-ALL------.
>>-SUM--(----+----------+--expression--)-----------------------><
"-DISTINCT-"

← →
Johnmen © (2002-12-27 10:26) [27]

>Lady D © (27.12.02 09:45)

Маша, у Вас просто какая-то юношеская горячность...:)))
см. ЮЮ © (27.12.02 09:54)

← →
diMAN © (2002-12-27 11:01) [28]

> Johnmen © (26.12.02 11:23)
> Где работает ?

> Val © (26.12.02 11:29)
> Где работает ?
> подозреваю, что нигде.

> MsGuns © (26.12.02 11:48)
> В Local SQL, я думаю, работать будет.

Вы вопрос внимательно читали
SUM -> NULL [D5, Paradox]
^^^^^^^
Естественно, что в Local SQL. И говоря что это будет 100% работать, я не лукавил, т.к. данный код у меня уже более трёх лет с успехом работает. А кто не верит - проверьте, а уже потом говорите что нигде не будет работать.

Господа, а вам не кажется, что вы идете не тем путем. Не проще ли будет добавить в таблицы на эти поля дефолтное значение 0, и проапдейтить существующие записи
update table set debit=0 where debit is null
update table set credit=0 where credit is null

И все!!! Все будет работать правильно. Работа на 5 минут. Я бы сделал именно так.

> Lady D © (27.12.02 09:45)
> Проблема в том, что написала об этом Lady...
По Вашему участию в других дискуссиях у меня, по крайней мере, сложилось впечатление, что Ваш уровень гораздо выше среднего уровня участников форума, НО: Вас опять подвело "невнимательное читание писанины" и Вы, как бы это при девушках сказать, к сожалению, лажанулсь :)
sum(all column_value) по синтаксису Local SQL эквивалентно
sum( column_value)
А девушек у нас любят, берегут, и уважают :), рискну выразить мнение подавляющего большинства участников форума :)

По сути вопроса: null в результате sum(column_value) получается, либо когда не просуммировано ни одной строки, либо когда суммируются только null`ы. Так как в Local SQL нет ф-ции типа nvl(nullable_expr,expr), которая в случае, если nullable_expr is null возвращала бы expr, в противном - ниче не трогала, прав Sergey13 © (27.12.02 11:42) - даже если Вы победите данный конкретный запрос, null`ы гадить Вам будут каждый день до скончания века

> Johnmen © (27.12.02 11:10)
> Где выполняется запрос ? Как выполняется ? Какой движок
> используется ?

Например, в моей программе. Всегда корректно 100% (через TQuery). D5, D6 + BDE + Paradox.

Вот работающий у меня фрагмент:
Screen.Cursor := crHourGlass; with WorkQuery do try SQL.Clear; SQL.Add("SELECT A.ShortName OrgName, CAST(""0""||CAST(B.Prihod AS CHAR(30)) AS MONEY) Prihod1, "); SQL.Add("CAST(""0""||CAST(C.Prihod AS CHAR(30)) AS MONEY) Prihod2, "); SQL.Add("CAST(""0""||CAST(D.Vernut AS CHAR(30)) AS MONEY) Vernut1, "); SQL.Add("CAST(""0""||CAST(E.Vernut AS CHAR(30)) AS MONEY) Vernut2, "); SQL.Add("CAST(""0""||CAST(F.Oplata AS CHAR(30)) AS MONEY) Oplata1, "); SQL.Add("CAST(""0""||CAST(G.Oplata AS CHAR(30)) AS MONEY) Oplata2"); SQL.Add("FROM "org.db" A LEFT OUTER JOIN "prihodbefore.db" B ON A.Code=B.OrgCode"); SQL.Add("LEFT OUTER JOIN "prihodbetween.db" C ON A.Code=C.OrgCode"); SQL.Add("LEFT OUTER JOIN "vernutbefore.db" D ON A.Code=D.OrgCode"); SQL.Add("LEFT OUTER JOIN "vernutbetween.db" E ON A.Code=E.OrgCode"); SQL.Add("LEFT OUTER JOIN "oplatabefore.db" F ON A.Code=F.OrgCode"); SQL.Add("LEFT OUTER JOIN "oplatabetween.db" G ON A.Code=G.OrgCode"); SQL.Add("WHERE A.ID=1"); SQL.Add("ORDER BY A.ShortName"); Open; DbiMakePermanent(handle, PChar(DataBaseTDir + "postavsvod.db"), true); Close; except Screen.Cursor := crDefault; ShowMessage("Ошибка!"); end;

Да, круть, нафиг. После приклейки нуля больше всего мне понравилось : LEFT OUTER JOIN. Видимо на "LEFT RIGHT OUTER INNER JOIN" BDE уже ругнулся.

>diMAN © (30.12.02 16:46)

То есть ты хочешь сказать, что есть поля (C.Prihod, D.Vernut, F.Oplata), в которых значение NULL ? И, кстати, каковы типы этих полей ?

>Johnmen © (30.12.02 17:39)
> То есть ты хочешь сказать, что есть поля (C.Prihod, D.Vernut, > F.Oplata), в которых значение NULL ? И, кстати, каковы типы
> этих полей ?

Да, именно это я и хочу сказать. Все эти поля типа ftCurrency. В них находятся сведения о суммах прихода товара, возврата товара, оплаты за товар по каждой из организаций за определённый период времени (любой - хоть 1 день, хоть 1 месяц, хоть 1 год). И естественно, за данный период конкретная оргганизация могла не получать (возвращать, оплачивать) товар, т.е. значения этих полей будут NULL.

> Delirium^.Tremens © (30.12.02 16:50)
> Да, круть, нафиг. После приклейки нуля больше всего мне
> понравилось : LEFT OUTER JOIN. Видимо на "LEFT RIGHT OUTER
> INNER JOIN" BDE уже ругнулся.

Я в этом ничего страшного не вижу. Я понимаю что слово OUTER можно ставить, а можно - и нет. Но мне так удобнее и нагляднее. Я привык делать так. К тому же в Local SQL Help -> OUTER join (файл localsql.hlp) в примерах дается именно такой (полный) вариант записи SQL-запроса.

>diMAN © (31.12.02 10:35)

Вывод однозначен - NULL реализован как нулевое значение, а это не одно и то же ! (в данном конкретном случае)

>И естественно, за данный период конкретная оргганизация могла
>не получать (возвращать, оплачивать) товар, т.е. значения этих
>полей будут NULL.

Почему же ? Кто указывает, что NULL ?

> Johnmen © (31.12.02 10:47)
> Почему же ? Кто указывает, что NULL ?

На это указывает то, как получаются таблицы prihodbefore.db, prihodbetween.db и т.д.

... SQL.Clear; SQL.Add("SELECT A.OrgCode, SUM(A.SummaWithNalog) Prihod FROM "opermain.db" A"); SQL.Add("WHERE A.CodeOper=1 AND A.DateOper < "" + DateToStr(WorkDateStart) + """); SQL.Add("GROUP BY A.OrgCode"); Open; DbiMakePermanent(handle, PChar(DataBaseTDir + "prihodbefore.db"), true); Close; SQL.Clear; SQL.Add("SELECT A.OrgCode, SUM(A.SummaWithNalog) Prihod FROM "opermain.db" A"); SQL.Add("WHERE A.CodeOper=1 AND A.DateOper BETWEEN "" + DateToStr(WorkDateStart) + "" AND "" + DateToStr(WorkDateEnd) + """); SQL.Add("GROUP BY A.OrgCode"); Open; DbiMakePermanent(handle, PChar(DataBaseTDir + "prihodbetween.db"), true); Close; ...

Таким образом мы вернулись к сути вопроса, что если в течение рабочего периода у организации не было прихода, то она не попадёт в таблицу "prihodbetween.db", а следовательно при объединении LEFT OUTER JOIN, поле C.Prihod (см. предыдущий пример) будет NULL. В чём я давно и легко убедился, открыв полученную таблицу через Database Desktop. Собственно это и побудило меня найти способ преобразования в SQL-запросе значения NULL в 0, что и было успешно достигнуто. Рабочий вариант запроса я и предложил автору вопроса.

P.S. Только что я создал таблицу с полями Code: ftAutoinc, Summa: ftMoney и добавил в неё пять записей, у которых поле Summa содержит NULL. Выполнил запрос SELECT SUM(Summa) FROM test, получив в результате NULL и выполнил запрос SELECT SUM(CAST("0"||CAST(Summa AS CHAR(30)) AS MONEY)) FROM test, получив в рузультате 0. Если Вы и на этот раз сомневаетесь, то проверьте собственноручно.

>diMAN © (04.01.03 12:41)

Последние уточнения по поводу P.S. :
Где, при помощи чего создана таблица ?
Где, при помощи чего выполнен запрос ?
Где, при помощи чего видны результаты ?

> Johnmen © (04.01.03 12:56)
> Последние уточнения по поводу P.S. :
> Где, при помощи чего создана таблица ?
> Где, при помощи чего выполнен запрос ?
> Где, при помощи чего видны результаты ?

Все действия сделаны при помощи Database Desktop (таблица Paradox).

SUM -> NULL Найти похожие ветки