CODEWITHSUNDEEP
sql-server
user1030181
user1030181

Reputation: 2015

Update statement SQL Server error for GROUP BY

UPDATE #IncrementalCreditHeaderResult
SET TotalDeliveredVolume = SUM(TSR.QuantityBilled) FROM dbo.ThruputServiceRendereds TSR WITH(NOLOCK)
INNER JOIN Services S ON S.ServiceId = TSR.ServiceID 
    AND  S.ServiceCode = 'TRAN' 
    AND TSR.TransactionCompleteDatetime BETWEEN @service_start_date AND @endDate
INNER JOIN Terminals T ON T.TerminalID = TSR.TerminalID 
    AND T.TerminalNumber = @terminal_number
INNER JOIN Contracts C ON C.ContractID = TSR.ContractID 
    AND C.ContractNumber = @contract_number
WHERE MonthPeriod = Format(@service_start_date, N'MMM-yy')
GROUP BY TotalDeliveredVolume

When I use group by I get this error:

Msg 157, Level 15, State 1, Procedure rpt_Test, Line 106

An aggregate may not appear in the set list of an UPDATE statement.

Upvotes: 0

Views: 46

Answers (2)

user1030181
user1030181

Reputation: 2015

Did this simplest approach

    DECLARE @TDV DECIMAL(18,7)
    SELECT @TDV = CAST(SUM(TSR.QuantityBilled) AS DECIMAL(18,7)) FROM dbo.ThruputServiceRendereds TSR WITH(NOLOCK)
    INNER JOIN Services S WITH(NOLOCK) ON S.ServiceId = TSR.ServiceID AND  S.ServiceCode = 'TRAN' AND TSR.TransactionCompleteDatetime BETWEEN @service_start_date AND @endDate
    INNER JOIN Terminals T WITH(NOLOCK) ON T.TerminalID = TSR.TerminalID AND T.TerminalNumber = @terminal_number
    INNER JOIN Contracts C WITH(NOLOCK) ON C.ContractID = TSR.ContractID AND C.ContractNumber = @contract_number

    UPDATE #IncrementalCreditHeaderResult
    SET TotalDeliveredVolume = @TDV
    WHERE MonthPeriod = Format(@service_start_date, N'MMM-yy')

Upvotes: 0

InbetweenWeekends
InbetweenWeekends

Reputation: 1414

The error gives you the hint. You cannot use SUM in the SET of an UPDATE statement.

An easy alternative is to create a #temp table and populate it with the aggregated results, then update.

I took a stab at it here - inferring what I could.

CREATE TABLE #tmp(QuantityBilled INT, ServiceID INT, TerminalID INT, ContractID INT)
INSERT INTO #tmp(QuantityBilled, ServiceID, TerminalID, ContractID)
SELECT SUM(TSR.QuantityBilled), TSR.ServiceID , TSR.TerminalID, TSR.ContractID
FROM dbo.ThruputServiceRendereds TSR WITH(NOLOCK)
WHERE TSR.TransactionCompleteDatetime BETWEEN @service_start_date AND @endDate
GROUP BY TSR.ServiceID

UPDATE #IncrementalCreditHeaderResult
SET TotalDeliveredVolume = TSR.QuantityBilled
FROM #tmp TSR 
INNER JOIN Services S ON S.ServiceId = TSR.ServiceID 
    AND  S.ServiceCode = 'TRAN' 
INNER JOIN Terminals T ON T.TerminalID = TSR.TerminalID 
    AND T.TerminalNumber = @terminal_number
INNER JOIN Contracts C ON C.ContractID = TSR.ContractID 
    AND C.ContractNumber = @contract_number
WHERE MonthPeriod = Format(@service_start_date, N'MMM-yy') -- Not sure what table this is from

DROP TABLE #tmp

Another alternative is to use Common Table Expressions (CTE)

;WITH CTE AS(
    SELECT SUM(QuantityBilled) as QuantityBilled, ServiceID , TerminalID, ContractID
    FROM dbo.ThruputServiceRendereds WITH(NOLOCK)
    WHERE TransactionCompleteDatetime BETWEEN @service_start_date AND @endDate
    GROUP BY ServiceID
)
UPDATE #IncrementalCreditHeaderResult
SET TotalDeliveredVolume = TSR.QuantityBilled
FROM CTE TSR -- rest should be the same.

A third option is to use a subquery:

UPDATE #IncrementalCreditHeaderResult
SET TotalDeliveredVolume = TSR.QuantityBilled
FROM (
    SELECT SUM(QuantityBilled) as QuantityBilled, ServiceID , TerminalID, ContractID
    FROM dbo.ThruputServiceRendereds WITH(NOLOCK)
    WHERE TransactionCompleteDatetime BETWEEN @service_start_date AND @endDate
    GROUP BY ServiceID
) TSR  -- rest should be the same.

Upvotes: 2

Related Questions