CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Deepak Jain
Deepak Jain

Reputation: 137

Need sum of column while selecting other value

I have a table like this:

   empID      name       amt      Date
   ------------------------------------
     1        mark        20     22-10
     1        mark        30     22-10
     2        kane        50     22-12  
     2        kane        60     22-12
     3        mike        60     22-10

and I want to get an output like that

 empID   name      amt      Date    TOTAL
 -----------------------------------------
     1     mark    20      22-10    220
     1     mark    30      22-10    220
     2     kane    50      22-12    220
     2     kane    60      22-12    220
     3     mike     60     22-10    220

I have used sum(amt) but it is returning only 1 row; I want other rows as well.

Upvotes: 0

Views: 62

Answers (3)

Gudwlk
Gudwlk

Reputation: 1157

Since you need to display the grand total value for each row you don't need to partition or group by. Therefore, you can use SUM(amt) OVER () .

 SELECT  *, 
 SUM(amt) OVER () AS [Total]     
 FROM tabe_c

Result

Upvotes: 0

Eli
Eli

Reputation: 2608

You need a windowing function

SELECT
     empid
    ,name
    ,amt
    ,[date]
    ,SUM(amt) OVER(PARTITION BY '') AS Total -- as you show it
    ,SUM(amt) OVER(PARTITION BY empID) AS Total -- as I think you want it
FROM t

Documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81960

You can use the window function sum() over() without any partition or order by

Example

Select *
      ,[Total] = sum(amt) over()
 From  YourTable

Upvotes: 2

Related Questions