CODEWITHSUNDEEP
sql-server
WraithNath
WraithNath

Reputation: 18013

SQL - Cant use aggregate of subquery in Group clause

Im trying to group by [DueDate] on the following query, but that date can be different depending on a setting. is there anyway I can make this work?

SELECT
(
    SELECT CASE 
    WHEN (SELECT TOP 1 [SiconCFMSetting].[SettingValue] FROM [SiconCFMSetting] WHERE [SiconCFMSetting].[SettingName]='UseAverageTimeToPay') = 'True'
    THEN ISNULL([SiconCFMForecastDate].[ForecastDate],DATEADD([DD],[SiconCFMSLCustomerAverageTimeToPayView].[Days],[SLPostedCustomerTran].[TransactionDate]))
    ELSE ISNULL([SiconCFMForecastDate].[ForecastDate],[SLPostedCustomerTran].[DueDate])
    END
) AS [DueDate],
SUM([SLPostedCustomerTran].[SalControlValueInBaseCurrency]) AS [Value]
FROM        [SLPostedCustomerTran]
INNER JOIN  [SLCustomerAccount]
ON          [SLCustomerAccount].[SLCustomerAccountID]
=           [SLPostedCustomerTran].[SLCustomerAccountID]
INNER JOIN  [SiconCFMSLCustomerAverageTimeToPayView]
ON          [SiconCFMSLCustomerAverageTimeToPayView].[SLCustomerAccountID]
=           [SLCustomerAccount].[SLCustomerAccountID]
LEFT JOIN   [SiconCFMForecastDate]
ON          [SiconCFMForecastDate].[ForecastDateForeignID]
=           [SLPostedCustomerTran].[SLPostedCustomerTranID]
AND         [SiconCFMForecastDate].[Deleted]=0
AND         [SiconCFMForecastDate].[ForecastDateSource]='SLPostedCustomerTran'
WHERE       ([SLPostedCustomerTran].[SYSTraderTranTypeID]=4 OR [SLPostedCustomerTran].[SYSTraderTranTypeID]=5)
AND         [SLPostedCustomerTran].[SalControlValueInBaseCurrency] <> 0
GROUP BY    
    CASE 
    WHEN (SELECT TOP 1 [SiconCFMSetting].[SettingValue] FROM [SiconCFMSetting] WHERE [SiconCFMSetting].[SettingName]='UseAverageTimeToPay') = 'True'
    THEN ISNULL([SiconCFMForecastDate].[ForecastDate],DATEADD([DD],[SiconCFMSLCustomerAverageTimeToPayView].[Days],[SLPostedCustomerTran].[TransactionDate]))
    ELSE ISNULL([SiconCFMForecastDate].[ForecastDate],[SLPostedCustomerTran].[DueDate])
    END

Error : Msg 144, Level 15, State 1, Line 26 Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Upvotes: 1

Views: 3103

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Do the aggregation in outer query. You can do this

with cte as
(
SELECT
(
    SELECT CASE 
    WHEN (SELECT TOP 1 [SiconCFMSetting].[SettingValue] FROM [SiconCFMSetting] WHERE [SiconCFMSetting].[SettingName]='UseAverageTimeToPay') = 'True'
    THEN ISNULL([SiconCFMForecastDate].[ForecastDate],DATEADD([DD],[SiconCFMSLCustomerAverageTimeToPayView].[Days],[SLPostedCustomerTran].[TransactionDate]))
    ELSE ISNULL([SiconCFMForecastDate].[ForecastDate],[SLPostedCustomerTran].[DueDate])
    END
) AS [DueDate],
[SLPostedCustomerTran].[SalControlValueInBaseCurrency] AS [Value]
FROM        [SLPostedCustomerTran]
INNER JOIN  [SLCustomerAccount]
ON          [SLCustomerAccount].[SLCustomerAccountID]
=           [SLPostedCustomerTran].[SLCustomerAccountID]
INNER JOIN  [SiconCFMSLCustomerAverageTimeToPayView]
ON          [SiconCFMSLCustomerAverageTimeToPayView].[SLCustomerAccountID]
=           [SLCustomerAccount].[SLCustomerAccountID]
LEFT JOIN   [SiconCFMForecastDate]
ON          [SiconCFMForecastDate].[ForecastDateForeignID]
=           [SLPostedCustomerTran].[SLPostedCustomerTranID]
AND         [SiconCFMForecastDate].[Deleted]=0
AND         [SiconCFMForecastDate].[ForecastDateSource]='SLPostedCustomerTran'
WHERE       ([SLPostedCustomerTran].[SYSTraderTranTypeID]=4 OR [SLPostedCustomerTran].[SYSTraderTranTypeID]=5)
AND         [SLPostedCustomerTran].[SalControlValueInBaseCurrency] <> 0
)
select DueDate,sum(Value)
from cte 
group by DueDate

To do this in singe query use OUTER APPLY

SELECT OA.[DueDate],
       Sum([SLPostedCustomerTran].[SalControlValueInBaseCurrency]) AS [Value]
FROM   [SLPostedCustomerTran]
       INNER JOIN [SLCustomerAccount]
               ON [SLCustomerAccount].[SLCustomerAccountID] = [SLPostedCustomerTran].[SLCustomerAccountID]
       INNER JOIN [SiconCFMSLCustomerAverageTimeToPayView]
               ON [SiconCFMSLCustomerAverageTimeToPayView].[SLCustomerAccountID] = [SLCustomerAccount].[SLCustomerAccountID]
       LEFT JOIN [SiconCFMForecastDate]
              ON [SiconCFMForecastDate].[ForecastDateForeignID] = [SLPostedCustomerTran].[SLPostedCustomerTranID]
                 AND [SiconCFMForecastDate].[Deleted] = 0
                 AND [SiconCFMForecastDate].[ForecastDateSource] = 'SLPostedCustomerTran'
       OUTER apply (SELECT CASE
                             WHEN (SELECT TOP 1 [SiconCFMSetting].[SettingValue]
                                   FROM   [SiconCFMSetting]
                                   WHERE  [SiconCFMSetting].[SettingName] = 'UseAverageTimeToPay') = 'True' THEN Isnull([SiconCFMForecastDate].[ForecastDate], Dateadd([DD], [SiconCFMSLCustomerAverageTimeToPayView].[Days], [SLPostedCustomerTran].[TransactionDate]))
                             ELSE Isnull([SiconCFMForecastDate].[ForecastDate], [SLPostedCustomerTran].[DueDate])
                           END) OA ([DueDate])
WHERE  ( [SLPostedCustomerTran].[SYSTraderTranTypeID] = 4
          OR [SLPostedCustomerTran].[SYSTraderTranTypeID] = 5 )
       AND [SLPostedCustomerTran].[SalControlValueInBaseCurrency] <> 0

Upvotes: 2

Related Questions