Как заставить SUM обрабатывать NULL

← →
Ольга (2006-09-22 10:51) [0]

Есть 2 таблицы - справочник объектов и данные по объектам.
Нужно посчитать суммы данных по объектам и датам, но так, чтобы сумма считалась только тогда, когда есть данные по всем объектам за дату, иначе сумма=NULL
Мой запрос выдает некорректные суммы (по тем данным, что есть, NULL игнорируется):
SELECT id_obj, dt, SUM(b.Value) FROM cls_objects a LEFT OUTER JOIN obj_data b ON a.id_obj=b.id_obj GROUP BY id_obj, dt

← →
Desdechado © (2006-09-22 10:55) [1]

> есть данные по всем объектам за дату
Как это определить?

> по тем данным, что есть, NULL игнорируется
Может, поставить INNER JOIN

← →
Sergey13 © (2006-09-22 10:56) [2]

По тем полям, по которым надо получать SUM желательно выставлять 0 по дефолту для избавления от NULL.
Если в запрос добавить еще и Count(*), то должны появиться и "пропавшие" записи.

← →
Johnmen © (2006-09-22 10:57) [3]

> NULL игнорируется

А должен складываться? :)
Или я что-то не понял вопроса...

← →
ЮЮ © (2006-09-22 11:04) [4]

1) Такой запрос вообще не выполнится, ибо поле id_obj есть в двух таблицах
2) NULL в таком запросе никак не может влиять на сумму, ибо в неё не входит

Нужно посчитать суммы данных по объектам и датам, но так, чтобы сумма считалась только тогда, когда есть данные по всем объектам за дату, иначе сумма=NULL

Тогда и запрос надо писать так, а не как придется

← →
Stanislav © (2006-09-22 11:04) [5]

Я правильно понял?

POLE
3
5
7
Null

SUM(POLE)=NULL

← →
Val © (2006-09-22 11:10) [6]

>[2] Sergey13 © (22.09.06 10:56)
да в mssql вроде есть аналог nvl - coalesce что-ли..зачем тут 0 дефолтный тогда..
в вопрос я тоже до конца не врулил :(

← →
Ольга (2006-09-22 11:11) [7]

> [5]

Именно так. Как это сделать, используя процедуру т.п., понятно.
Хотелось бы запросом, если возможно.

← →
ЮЮ © (2006-09-22 11:13) [8]

> в вопрос я тоже до конца не врулил :(

Не за все даты нужна численная сумма, а лишь за те, где сумма не NULL для ВСЕХ объектов, иначе считать её NULL, даже если это и не так.
Если я правильно понял

← →
Ega23 © (2006-09-22 11:15) [9]

Использовать IsNull:
SELECT id_obj, dt, SUM(IsNull(b.Value, 0)) FROM cls_objects a LEFT OUTER JOIN obj_data b ON a.id_obj=b.id_obj GROUP BY id_obj, dt

← →
Ольга (2006-09-22 11:15) [10]

> [6]

Эт не то. Есть в T-SQL аналог ISNULL, но она не умеет делать, как я хочу -
исли встретился хоть один NULL в составляющих, то вся сумма = NULL

← →
Stanislav © (2006-09-22 11:19) [11]

Можно так
Select (Case when c>0 then NULL ELSE A END) as SUMMA FROM (
Select SUM (ALL N) as A, c=SUM(Case WHEN N is NUll then 1 else 0 end) FROM
(SELECT 1 as N
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT NULL) as Q) as Q2

← →
Johnmen © (2006-09-22 11:20) [12]

> исли встретился хоть один NULL в составляющих, то вся сумма
> = NULL

Не может быть! :)

← →
Stanislav © (2006-09-22 11:22) [13]

Вобще это можно без вложеных запросов сделать.

← →
Stanislav © (2006-09-22 11:24) [14]

ВОТ ТАК:
Select CASE WHEN SUM(Case WHEN N is NUll then 1 else 0 end)>0 THEN NULL ELSE SUM(N)END FROM
(SELECT 1 as N
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 3) as Q

← →
Sergey13 © (2006-09-22 11:26) [15]

> [6] Val © (22.09.06 11:10)
> зачем тут 0 дефолтный тогда..
Я всегда так делать стараюсь. Конкретно тут это пофиг, а вот на SUM(Field1*Field2) можно здорово огрести.

← →
ЮЮ © (2006-09-22 11:27) [16]

Если в [8] я правильно понял, то

DECLARE @ObjectsCnt int
SET @ObjectsCnt = SELECT COUNT(*) FROM cls_objects

SELECT
id_obj,
CASE Cnt WHEN @ObjectsCnt THEN SumValue ELSE NULL END MySumValue
FROM (
SELECT
a.id_obj, dt,
SUM(b.Value) SumValue, COUNT(DISTINCT b.id_obj) Cnt
FROM
cls_objects a
LEFT OUTER JOIN obj_data b ON a.id_obj=b.id_obj
GROUP BY id_obj, dt
) gr

← →
Ольга (2006-09-22 11:27) [17]

> [9]

Оказывается конструкция (раньше я ее использовала) SUM(IsNull(b.Value, 0)) вообще бессмысленная, т.к. = SUM(b.Value)

> [11]

Да, хотелось бы попроще...

← →
sniknik © (2006-09-22 11:27) [18]

SELECT CASE Sum(isNull(f-f,1)) WHEN 0 THEN Sum(f) ELSE Null END FROM Table1

← →
Stanislav © (2006-09-22 11:27) [19]

Johnmen © (22.09.06 11:20) [12]

Такое действительно бывает нужно, я допустим это применял для материальных спецификаций, пока все материалы не проценены сумма не должна читаться.
Странно почему нет стандартной фунции в SQL, хотя MSSQL выдает сообщение что NULL встречаются в агрегатной функции.

← →
ЮЮ © (2006-09-22 11:28) [20]

GROUP BY a.id_obj, dt

← →
ЮЮ © (2006-09-22 11:30) [21]

- Дохтур, почему меня все игнорируют?

← →
sniknik © (2006-09-22 11:32) [22]

> Не может быть! :)
конечно не может, это ему просто хочется чтобы так было...

← →
Johnmen © (2006-09-22 11:35) [23]

> sniknik © (22.09.06 11:32) [22]
> конечно не может, это ему просто хочется чтобы так было...

А-а-а...Понятно...

← →
Val © (2006-09-22 11:35) [24]

[17] Ольга (22.09.06 11:27)
>Оказывается конструкция..
ну да, смысл имеет IsNull(SUM(b.Value), 0) :)

← →
Ольга (2006-09-22 11:39) [25]

> ЮЮ © (22.09.06 11:27) [16]

Вот эту штуку некуда поставить:
DECLARE @ObjectsCnt int
SET @ObjectsCnt = SELECT COUNT(*) FROM cls_objects
Этот запрос - вьюшка

← →
ЮЮ © (2006-09-22 11:43) [26]

а MS SQL какой?
Может не во вьюшку, а в UDF положить?

← →
Ольга (2006-09-22 11:48) [27]

> [26]

Видимо придется. А хотелось малой кровью - не трогать exe-шник.

на крайний случай добавить это числ к каждой записи

SELECT
id_obj,
CASE Cnt WHEN o.ObjectsCnt THEN SumValue ELSE NULL END MySumValue
FROM (
SELECT
a.id_obj, dt,
SUM(b.Value) SumValue, COUNT(DISTINCT b.id_obj) Cnt
FROM
cls_objects a
LEFT OUTER JOIN obj_data b ON a.id_obj=b.id_obj
GROUP BY a.id_obj, dt
) gr
JOIN
(
SELECT COUNT(*) ObjectsCnt FROM cls_objects
) o

Удалено модератором

> Видимо придется. А хотелось малой кровью - не трогать exe-
> шник.

А что во вьюхе нельзя hfpdt SELECT * From dbo.SomeUDF?
Уж извините, вьюхи не пользую, имея UDF :)

Удалено модератором

Стоп. Я фигню сморозил. По-моему, как-то это дело (IsNull) в условие JOIN можно добавить.
Сейчас попробую поискать, где-то что-то такое у меня встречалось...

> Stanislav © (22.09.06 11:50) [29]
> Чем не устраевает - Stanislav © (22.09.06 11:24) [14]

Трудно уловить смысл на табличке

1
1
1
3

Я, например, не смог :) Ты бы на предложенных структурах предложил.

Удалено модератором

> Трудно уловить смысл на табличке
добавь запись с NULL и посмотри разницу.

> Ты бы на предложенных структурах предложил.
т.е. готовый "продукт" для копипасте?

Удалено модератором

Как заставить SUM обрабатывать NULL Найти похожие ветки