Данные не суммируются должным образом

ProgramBox

Данные не суммируются должным образом

Post author:admin
Запись опубликована:31 января, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я создал запрос, который я не могу правильно суммировать.

Первый шаг

 SELECT DISTINCT
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date
,SUM(det.ordd_mn_prev_paid_amt) as Expiring

--,CASE WHEN det.ordd_mn_prev_paid_amt > det.ordd_mn_billed_amt
--THEN det.ordd_mn_prev_paid_amt - det.ordd_mn_billed_amt
--ELSE 0
--END AS Upsell

FROM ccmast_restore_20201021.dbo.lti_ord o
INNER JOIN ccmast_restore_20201021.dbo.lti_orddet det ON o.id = det.ordd_id
INNER JOIN ccmast_restore_20201021.dbo.ContractItem CI ON CI.ContractItemID = det.ordd_ContractItemID
WHERE 
--det.ordd_mn_billed_amt > 0
CI.ContractItemID =327
AND det.ordd_mn_end_date BETWEEN '05/31/2020' AND '11/01/2020' --Expiring
GROUP BY 
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date
--,det.ordd_mn_prev_paid_amt
--,det.ordd_mn_billed_amt

ORDER BY 
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date

Это работает, я получу 1 строку данных
Когда я добавляю следующий оператор case, у меня возникают проблемы

Второй шаг

 
SELECT
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date
,SUM(det.ordd_mn_prev_paid_amt) as Expiring

,CASE WHEN det.ordd_mn_prev_paid_amt > det.ordd_mn_billed_amt
THEN det.ordd_mn_prev_paid_amt - det.ordd_mn_billed_amt
ELSE 0
END AS Upsell

FROM ccmast_restore_20201021.dbo.lti_ord o
INNER JOIN ccmast_restore_20201021.dbo.lti_orddet det ON o.id = det.ordd_id
INNER JOIN ccmast_restore_20201021.dbo.ContractItem CI ON CI.ContractItemID = det.ordd_ContractItemID
WHERE 
--det.ordd_mn_billed_amt > 0
CI.ContractItemID =327
AND det.ordd_mn_end_date BETWEEN '05/31/2020' AND '11/01/2020' --Expiring
GROUP BY 
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date
,det.ordd_mn_prev_paid_amt
,det.ordd_mn_billed_amt

ORDER BY 
det.ordd_ContractItemID
,det.ordd_mn_start_date
,det.ordd_mn_end_date
,det.ordd_mn_prev_paid_amt
,det.ordd_mn_billed_amt

Результаты

 ordd_ContractItemID ordd_mn_start_date       ordd_mn_end_date          Expiring  Upsell
327                 2017-10-01 00:00:00.000  2020-09-30 00:00:00.000    0.00     0.00
327                 2017-10-01 00:00:00.000  2020-09-30 00:00:00.000    0.00     0.00
327                 2017-10-01 00:00:00.000  2020-09-30 00:00:00.000    0.00     0.00
327                 2017-10-01 00:00:00.000  2020-09-30 00:00:00.000    36331.86 0.00

Я также попробовал оператор IIF с теми же дублированными результатами.

 
--,IIF(det.ordd_mn_prev_paid_amt > det.ordd_mn_billed_amt,(det.ordd_mn_prev_paid_amt - det.ordd_mn_billed_amt),0) AS Upsell

1.Вы агрегируете det.ordd_mn_prev_paid_amt и группируете их; если вы хотите что-то агрегировать, этого не должно быть в GROUP BY . Кроме того, в первом запросе у вас есть как a DISTINCT , так и a GROUP BY . Это почти всегда означает, что либо ваш GROUP BY неверен, либо DISTINCT является излишним и просто добавляет ненужные накладные расходы.

2. Итак, в чем именно проблема. Вы дали нам код и результат, который он дает, мы не знаем, чего вы хотите добиться

3. Я хочу иметь одну строку вывода вместо 4.

4. Я удалил Distinct из select . Если я удаляю —,det.ordd_mn_prev_paid_amt —,det.ordd_mn_billed_amt из группы by и порядка BY, я получаю недопустимую ошибку в списке выбора

Ответ №1:

Похоже, вы пытаетесь оценить соотношение затрат и прибыли. Попробуйте выполнить суммирование во внутреннем select, а затем выполнить оператор CASE.

 SELECT  ordd_ContractItemID
        ,ordd_mn_start_date
        ,ordd_mn_end_date
        ,t.Cost
        ,t.Profit
        ,CASE
             WHEN t.Cost > t.Profit THEN t.Cost - t.Profit
             ELSE 0
         END AS Upsell
FROM    (
            SELECT  det.ordd_ContractItemID
                    ,det.ordd_mn_start_date
                    ,det.ordd_mn_end_date
                    ,SUM(det.ordd_mn_prev_paid_amt) AS Cost
                    ,SUM(det.ordd_mn_billed_amt) AS Profit
            FROM    dbo.lti_ord AS o
                    INNER JOIN dbo.lti_orddet AS det ON o.id = det.ordd_id
                    INNER JOIN dbo.ContractItem AS CI ON CI.ContractItemID = det.ordd_ContractItemID
            WHERE   1 = 1
                    AND CI.ContractItemID = 327
                    AND det.ordd_mn_end_date BETWEEN '05/31/2020' AND '11/01/2020'  --Expiring
            GROUP BY
                    det.ordd_ContractItemID
                    ,det.ordd_mn_start_date
                    ,det.ordd_mn_end_date
        ) AS t;

1. Прошло некоторое время. Как мне это сделать?

2. Нажмите кнопку с галочкой слева от ответа 🙂

3. Вы можете использовать агрегированные данные в СЛУЧАЕ. Вы также можете использовать ОБРАЩЕНИЕ.

4. Это тоже неверно. rextester.com/ZABRN14916

Ответ №2:

GROUP BY должна соответствовать требуемой вам группировке. Другие столбцы так или иначе попадают в агрегированные данные. Для общего увеличения продаж СУММИРУЙТЕ увеличение продаж по каждой записи. Может быть, что-то вроде приведенного ниже. Обратите внимание, что DISTINCT не требуется, поскольку GROUP BY уже является distinct .

 SELECT det.ordd_ContractItemID, 
    det.ordd_mn_start_date, 
    det.ordd_mn_end_date, 
    SUM(det.ordd_mn_prev_paid_amt) as [Expiring],
    SUM(
        CASE WHEN det.ordd_mn_prev_paid_amt > det.ordd_mn_billed_amt 
                THEN det.ordd_mn_prev_paid_amt - det.ordd_mn_billed_amt 
            ELSE 0
            END
        ) AS [Upsell]
FROM ccmast_restore_20201021.dbo.lti_ord o
INNER JOIN ccmast_restore_20201021.dbo.lti_orddet det 
ON o.id = det.ordd_id
INNER JOIN ccmast_restore_20201021.dbo.ContractItem CI 
ON CI.ContractItemID = det.ordd_ContractItemID
WHERE CI.ContractItemID = 327
    AND det.ordd_mn_end_date BETWEEN '05/31/2020' AND '11/01/2020' --Expiring
GROUP BY det.ordd_ContractItemID, 
    det.ordd_mn_start_date, 
    det.ordd_mn_end_date
ORDER BY det.ordd_ContractItemID, 
    det.ordd_mn_start_date, 
    det.ordd_mn_end_date