CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Inner Select with SUM

I am trying to get the sum of 2 values in my inner select but get an error about the aggregate function.

I am just trying to understand how I need to format this differently to get it working.

Is there a different order I need to do this in?

 (SELECT   CONVERT(VARCHAR,D.[booksCost],1) AS booksCost, 
           CONVERT(VARCHAR,D.[tuitionCost],1) AS tuitionCost,
           SUM (D.[booksCost] + D.[tuitionCost]) AS totalCost,
           D.[className]
  FROM   tuitionApplicationClasses AS D
  WHERE  applicationID = A.[applicationID]
  FOR    XML PATH ('classData'), TYPE, ELEMENTS),


Column 'tuitionApplicationClasses.booksCost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 156

Answers (3)

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

When not doing an operation on a field you must include it in a GROUP BY statement that contains the other fields not used in the operation functions.

Upvotes: 0

CodeNewbie
CodeNewbie

Reputation: 2091

SUM is an aggregate function that is applied over the specified column in a query. What you are trying to do is simply add the first two columns in every row, so you need to modify the query to drop the SUM keyword.

(SELECT   CONVERT(VARCHAR,D.[booksCost],1) AS booksCost, 
       CONVERT(VARCHAR,D.[tuitionCost],1) AS tuitionCost,
       (D.[booksCost] + D.[tuitionCost]) AS totalCost,
       D.[className]
FROM   tuitionApplicationClasses AS D
WHERE  applicationID = A.[applicationID]
FOR    XML PATH ('classData'), TYPE, ELEMENTS),

Upvotes: 1

MikkaRin
MikkaRin

Reputation: 3084

Try use SUM() OVER(PARTITION BY..)

(SELECT   CONVERT(VARCHAR,D.[booksCost],1) AS booksCost, 
           CONVERT(VARCHAR,D.[tuitionCost],1) AS tuitionCost,
           SUM (D.[booksCost] + D.[tuitionCost]) OVER (PARTITON BY applicationID) AS totalCost,
           D.[className]
  FROM   tuitionApplicationClasses AS D
  WHERE  applicationID = A.[applicationID]
  FOR    XML PATH ('classData'), TYPE, ELEMENTS),

Upvotes: 0

Related Questions