Search code examples
sqlsql-serversumcorrelated-subquery

How to create a result from sql server with a summed column

Hello what I want is to write a query which will fetch me 3 column:-

  1. nvarchar column1
  2. integer Values column2
  3. single cell of the summed column2

is it possible , I am getting the following error:-

Msg 8120, Level 16, State 1, Line 1 Column 'tablename.columnname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What is the correct procedure to get data in the format I wish to get.

Edit

Jaques' answer works but I dont get what I want. What I want is:

column 1 PID | column 2 SID | column 3 | column 4 | Column 5(Total of 4)
-------------------------------------------------------------------------
1            | 1            | ABC      | 125.00   | 985.00
2            | 2            | XYZ      | 420.00   |
3            | 3            | DEF      | 230.00   |
4            | 4            | GHI      | 210.00   |
Solution

  • You can do it in the following way, because you need to add all non aggregated values in the group by, which makes it difficult

    Select column1, column2, SUM(column2) OVER (PARTITION BY column1) as Total from [Table]

    This should work.

    You can do it with a subselect from your edited answer, but why do you want it like that?

    Select Column1, Column2, Column3, Column4, (Select SUM(Column4) from Table) as Column 5 from Table