The table with the data that I have
In the above table I have the columns : weekNumber , weeklyHours , points_Rewarded.
There are four employees : a,b,c,d
I have the values for week1,week2,week3, and so on ( I can have data for many more weeks also such as week4,week5, etc)
I want to write a query such that after passing the query I get the total of the weeklyHours and points_Rewarded for each employee in a new table.
The kind of table that the query should give me is here the desired table that I want after passing the query
Please help me with the query.
Thanks in advance.
You can use
GROUP BY to achieve aggregate values. In your case your are looking for
DECLARE @tbl TABLE(EmployeeID INT, EmployeeName VARCHAR(100),WeekNumber VARCHAR(100),WeeklyHours INT,pointsRewarded INT); INSERT INTO @tbl VALUES (1,'a','week1',10,20) ,(2,'b','week1',1,20) ,(3,'c','week1',20,20) ,(4,'d','week1',30,30) ,(1,'a','week2',11,10) ,(2,'b','week2',44,10) ,(3,'c','week2',5,10) ,(4,'d','week2',6,40) ,(1,'a','week3',7,10) ,(2,'b','week3',88,10) ,(3,'c','week3',9,10) ,(4,'d','week3',0,10); SELECT tbl.EmployeeID ,tbl.EmployeeName ,SUM(tbl.WeeklyHours) AS Total_Weekly_Hours ,SUM(pointsRewarded) AS Total_Points FROM @tbl AS tbl GROUP BY tbl.EmployeeID, tbl.EmployeeName