Функция SUM в SubQuery с использованием Group By
У меня есть таблица, в которой хранятся данные счетов-фактур различных поставщиков. Я пытаюсь написать запрос, чтобы определить, на какого поставщика я потратил больше всего денег, поэтому в настоящее время я написал запрос, который получает сумму InvoiceTotals при группировке по идентификатору поставщика. Меня сбивает с толку то, что мне нужно учитывать InvoiceType - если это «кредит», то InvoiceTotal для этого на самом деле отрицательный (поэтому его следует вычесть). CurrentStatus также должен быть только «Оплачено».
Например, в таблице ниже я ожидаю, что SUP-2 будет возвращен как наибольшая сумма, общая сумма которой составит 700,00.
Первоначально я создал следующий запрос, который просто суммирует все суммы и группы по поставщику, но не учитывает кредиты/негативы:
SELECT SUM(InvoiceTotal), SupplierID FROM Supplier_Invoices WHERE CurrentStatus IN ('Paid') GROUP BY SupplierID
Я попытался СУММИРОВАТЬ отрицательные/кредитные суммы отдельно и вычесть их из СУММЫ Дебетов, но все TotalAmts по какой-то причине отображаются как NULL для всех из них (если я удалю TOP 1, я смогу увидеть всю таблицу):
SELECT TOP 1
((SELECT SUM(InvoiceTotal)
FROM Supplier_Invoices m
WHERE CurrentStatus IN ('Paid')
AND InvoiceType != 'CREDIT'
AND m.SupplierID = t.SupplierID) -
(SELECT SUM(InvoiceTotal)
FROM Supplier_Invoices h
WHERE CurrentStatus IN ('Paid')
AND InvoiceType = 'CREDIT'
AND h.SupplierID = t.SupplierID)) AS TotalAmt,
t.SupplierID
FROM Supplier_Invoices t
WHERE CurrentStatus IN ('Paid')
GROUP BY SupplierID
ORDER BY TotalAmt desc
Как я могу правильно вернуть поставщика с самым высоким значением InvoiceTotal, принимая во внимание тот факт, что некоторые значения InvoiceTotal являются отрицательными, если они являются кредитными? Я чувствую, что должен быть гораздо лучший способ сделать это, чем я сейчас пытаюсь.
Ответы 4
Это решение выполняет суммирование выражения case. Если вам нужен третий тип счета, например «Исправление», это будет самое простое решение.
SELECT TOP 1
SupplierID,
SUM(CASE InvoiceType
WHEN 'Debit' THEN InvoiceTotal
WHEN 'Credit' THEN -InvoiceTotal
Else 0 END) as SumTotal
FROM Supplier_Invoices
WHERE CurrentStatus='Paid'
GROUP BY SupplierID
ORDER BY SumTotal desc
Перед фильтрацией:
После сортировки и ТОП 1
SELECT top 1
SupplierID,
SUM(InvoiceTotal * IIF(InvoiceType = 'Debit', 1, -1)) AS Suplier_total,
rnk = RANK() OVER(ORDER BY SUM(InvoiceTotal * IIF(InvoiceType = 'Debit', 1, -1)) DESC)
FROM Supplier_Invoices
WHERE CurrentStatus = 'PAID'
GROUP BY SupplierID
ORDER BY rnk
Здесь подзапрос просто умножает сумму на 1 или -1 в зависимости от типа счета, затем ранжирует результаты и, наконец, выбирает первое 1 значение.
Вот это в действии с использованием вашего примера данных.
Интересное решение. Пожалуйста, объясните преимущества расчета rnk. Разве описание ORDER BY Suplier_total не работает так же хорошо?
Вы правы, это сработало бы и было бы проще. Кажется, я начал с идеи вернуть несколько значений на основе ранга с помощью подзапроса, а затем передумал на полпути! Слишком много работы и слишком много тепла..:)
Вы можете попытаться манипулировать данными перед агрегированием.
WITH cte AS (
SELECT SupplierID
, (
CASE InvoiceType
WHEN 'Credit' THEN -InvoiceTotal
ELSE InvoiceTotal
END
) AS InvoiceTotal
FROM Supplier_Invoices
WHERE CurrentStatus = 'Paid'
)
SELECT SupplierID, SUM(InvoiceTotal) AS TotalAmt
FROM cte
GROUP BY SupplierID
ORDER BY TotalAmt DESC
Дальше, я думаю, все предельно ясно.
SELECT TOP(1) SupplierID, SUM(IIF(InvoiceType='Credit',-1.0,1.0) *InvoiceTotal) AS SumTotal
FROM #Supplier_Invoices
WHERE CurrentStatus = 'Paid'
GROUP BY SupplierID
ORDER BY 2 DESC
Мне кажется, что текст «Кредит» и «Дебет» используется вместо того, чтобы разрешать отрицательные числа в столбце InvoiceTotal. Будет ли проще, если вы отобразите в пользовательском интерфейсе «Кредит» или «Дебет» в зависимости от того, была ли общая сумма больше или меньше нуля, и удалили столбец «Тип счета»?