Агрегат SQL Query может не отображаться в предложении WHERE

У меня есть эта инструкция SQL, и SQL Server дает мне следующую ошибку:

Агрегат может не отображаться в предложении WHERE, если он не находится в подзапросе, содержащемся в предложении HAVING или в списке выбора.

SELECT 
  SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount
  , M1.BillingID
  , M2.Name
  , M2.DelinquentDaysThreshold
  , M2.DelinquentAmountThreshold
  , DATEDIFF(d, MIN(BillingDate),GETDATE()) as DaysLate
FROM Invoices M1 
LEFT JOIN ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE 
  InvoiceTotal <> AmountApplied
  AND M2.DelinquentDaysThreshold > DATEDIFF(d, MIN(BillingDate),GETDATE())
  OR (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold
GROUP BY 
  M1.BillingID
  , M2.Name
  , M2.DelinquentDaysThreshold
  , M2.DelinquentAmountThreshold

В предложении where я хочу только извлекать записи, где самая старая неоплаченная дата выставления счетов больше, чем DelinquentDaysThreshhold (в днях), или значение PastDueAmount (расчетное значение) больше, чем DelinquentAmountThreshold.
По какой-то причине SQL Server не любит агрегированные суммы.

Ответы

Ответ 1

Используйте HAVING как подсказано в сообщении об ошибке, которое требует GROUP BY:

SELECT
    SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount, 
    M1.BillingID, M2.Name, 
    M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
    DATEDIFF(d, MIN(BillingDate),GETDATE()) as DaysLate
FROM
    Invoices M1
    LEFT JOIN
    ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE
    InvoiceTotal <> AmountApplied
    AND
    M2.DelinquentDaysThreshold > DATEDIFF(d, MIN(BillingDate),GETDATE())
GROUP BY
    M1.BillingID, M2.Name, 
    M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
    DATEDIFF(d, MIN(BillingDate),GETDATE())
HAVING
    (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold

Ответ 2

Используйте "Наличие" для использования с агрегатными функциями.

Имеет ссылку: http://msdn.microsoft.com/en-us/library/ms180199.aspx

Ответ 3

В предложении where я хочу получить только те записи, где самая старая неоплаченная дата выставления счетов больше, чем DelinquentDaysThreshhold (в днях)

Некоторые соображения производительности с предложением HAVING:

Когда вы используете GROUP BY с предложением HAVING, предложение GROUP BY делит строки на группы сгруппированных строк и агрегирует их значения, а затем предложение HAVING исключает нежелательные агрегированные группы. Во многих случаях вы можете написать свой оператор select, чтобы он содержал только предложения WHERE и GROUP BY без предложения HAVING.

В качестве альтернативы предложению HAVING вы также можете использовать производные таблицы для решения этой проблемы:

SELECT t1.SomeID, t1.SomeThreshold, totals.NumericTotal
FROM   Table1 t1
       INNER JOIN (
          SELECT SomeID, SUM(SomeNumericValue) NumericTotal
          FROM Table1
          Group By SomeID
       ) totals
           ON t1.SomeID = totals.SomeID
WHERE totals.NumericTotal > t1.SomeThreshold