CODEWITHSUNDEEP
sql-server
Erix_TK
Erix_TK

Reputation: 35

Combine two results in one row

EmpID Name Date Earn

1 A 7/1/2014 2

1 A 7/1/2014 4

1 A 7/2/2014 1

1 A 7/2/2014 2

2 B 7/1/2014 5

2 B 7/2/2014 5

I would like combine two results in one row as below.here is my statement but i want to find the solution to get the Total_Earn?. Thank

"SELECT EmpID, Name, Date, Sum(earn) FROM employee WHERE Date between DateFrom and DateTo GROUP BY EmpID, Name, Date"

EmpID Name Date Earn Total_Earn

1 A 7/2/2014 3 9

2 B 7/2/2014 5 10

Upvotes: 0

Views: 62

Answers (2)

hotforfeature
hotforfeature

Reputation: 2588

Try this. Substitute the date for whatever value you want.

SELECT table1.EmpID, table1.Name, table1.Date, table1.Earn, table2.Total_Earn

FROM
    (SELECT EmpID, Name, Date, Earn
    FROM yourtablename    
    WHERE Date = "2014-07-02"
    GROUP BY EmpID) table1
LEFT JOIN 
    (SELECT EmpID, SUM(Earn)
    FROM yourtablename
    WHERE Date <= "2014-07-02"
    GROUP BY EmpID) table2
ON table1.EmpID = table2.EmpID

This will perform two SELECTs and join their results. The first select (defined as table1) well select the employee ID and earnings for the specified date.

The second statement (defined as table2) will select the total earnings for an employee up to and including that date.

The two statements are then joined together according to the employee ID.

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

It looks like you want the Max date and the Sum of Earn for each employee. Assuming you want one record for each ID/Name, you would do this:

select EmpID, Name, Max(Date), Sum(Earn) 
from YourTableName
group by EmpID, Name

Upvotes: 1

Related Questions