I have a query that returns a number of columns, including ColumnA (which is numerical). I want to add an additional column to the end of the query that returns the sum of ColumnA

SELECT
    ColumnA,
    SUM(ColumnA) AS ColumnB
  FROM 
    Table 

The code above doesn't work, but I'm not sure how to create something that will.

CodePudding user response：

Something like

SELECT
  ColumnA,
  ColumnASum
  FROM Table
  LEFT JOIN (SELECT SUM(columnA) ColumnASum FROM Table)
  ON TRUE;

Should work

CodePudding user response：

You could create a variable of the SUM() first.

DECLARE @ColumnB int
SET @ColumnB = (SELECT SUM(ColumnA) FROM Table)

SELECT ColumnA, @ColumnB
FROM Table

This should give you what you need.

CodePudding user response：

I would use CROSS APPLY.

SELECT Table.ColumnA
     ,CA.ColumnB
FROM Table
CROSS APPLY (SELECT SUM(ColumnA) ColumnB FROM Table) CA

You basically define a subquery that outputs an aggregate value that you can have as another column.

CodePudding user response：

I think you need this query:

SELECT ColumnA, (SELECT SUM(ColumnA) FROM table) as ColumnB
FROM table