CODEWITHSUNDEEP
sqlsql-server
Aryan M
Aryan M

Reputation: 657

How to get the sum of a column value as a new column in the SQL result set

I have a table known as payment plans, I need to get the result set as in the below table. 

CREATE TABLE PaymentPlans
(
PaymentPlanID           int, 
EmployeeID              int,        
PaidToDate              money
)

INSERT INTO PaymentPlans VALUES (1,1,100)
INSERT INTO PaymentPlans VALUES (2,1,200)
INSERT INTO PaymentPlans VALUES (3,1,150)

In my select query, i need an extra column which provides me the sum of 'PaidToDate'. I'm trying with the below query, where i'm getting the result set for 'Total amount paid', same as 'Paid to date'.

SELECT PaymentPlanID, EmployeeID, PaidToDate, SUM(PaidToDate) AS 'TOTAL AMOUNT PAID' FROM PaymentPlans group by PaymentPlanID, EmployeeID, PaidToDate

SELECT a.PaymentPlanID, a.EmployeeID, a.PaidToDate, SUM(a.PaidToDate) AS 
'TOTAL AMOUNT PAID' FROM (
SELECT PaymentPlanID, EmployeeID, PaidToDate FROM PaymentPlans
)a
GROUP BY a.PaymentPlanID, a.EmployeeID, a.PaidToDate

On both these scenarios, I'm getting the result as below,

PaymentPlanID   EmployeeID  PaidToDate  TotalAmountPaid
1               1           100.00      100.00
2               1           200.00      200.00
3               1           150.00      150.00

Where as i need result as below,

PaymentPlanID EmployeeID    PaidToDate    TotalAmountPaid
1             1             100.00        450.00
2             1             200.00        450.00
3             1             150.00        450.00

Please let me know what am i missing in my query.

Upvotes: 0

Views: 970

Answers (5)

Esperento57
Esperento57

Reputation: 17472

For complete the discution with Larnu,

method 1:

SELECT PaymentPlanID,
       EmployeeID,
       PaidToDate,
       SUM(PaidToDate) OVER (partition by EmployeeID order by PaymentPlanID) AS TotalAmountPaidByEmployeeID,
       SUM(PaidToDate) OVER () AS TotalAmountPaid
FROM dbo.PaymentPlans;

Method 2:

with Total as 
(
select EmployeeID, sum(PaidToDate) TotalAmountPaidByEmployeeID
from dbo.PaymentPlans
group by EmployeeID
)
SELECT f1.*, f2.TotalAmountPaidByEmployeeID
FROM dbo.PaymentPlans f1 inner join Total f2 on f1.EmployeeID=f2.EmployeeID;

Method 3

SELECT f1.*, f3.TotalAmountPaidByEmployeeID
FROM dbo.PaymentPlans f1
cross apply
(
  select sum(PaidToDate) TotalAmountPaidByEmployeeID from dbo.PaymentPlans f2
  where f1.EmployeeID=f2.EmployeeID
) f3

Upvotes: 1

Ryan Bedard
Ryan Bedard

Reputation: 431

In the case where you have multiple customer ids and you want each row to have that customer's total amount paid you would need to join to a sub query as follows

SELECT 
   a.PaymentPlanID, 
   a.EmployeeID, 
   a.PaidToDate, 
   b.TotalPaidToDate AS 'TOTAL AMOUNT PAID' 
FROM PaymentPlans a
INNER JOIN ( 
    SELECT 
       EmployeeID, 
       SUM(PaidToDate) AS TotalPaidToDate 
    FROM PaymentPlans 
    GROUP BY EmployeeID
) b
ON a.EmployeeID = b.EmployeeID

Upvotes: 2

Prashant Pimpale
Prashant Pimpale

Reputation: 10697

I would use Over clause that might be executed fast if the underlying records are more.

SELECT 
      PaymentPlanID,EmployeeID,
      PaidToDate,SUM(PaidToDate) OVER () as total
FROM PaymentPlans

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try below way 

select *,(select sum(PaidToDate) from  PaymentPlans) as total from PaymentPlans 

PaymentPlanID   EmployeeID  PaidToDate  total
1                  1         100.00    450.00
2                  1         200.00    450.00
3                  1        150.00     450.00

Upvotes: 2

Thom A
Thom A

Reputation: 95557

You can use the OVER clause. You might need to include the PARTITION BY clause, depending on your requirement.

SELECT PaymentPlanID,
       EmployeeID,
       PaidToDate,
       SUM(PaidToDate) OVER () AS TotalAmountPaid
FROM dbo.PaymentPlans;

Upvotes: 2

Related Questions