CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005sql-server-2012
Shivam Sharma
Shivam Sharma

Reputation: 107

Add values of a column and display added result in new table

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.

Upvotes: 1

Views: 159

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

You can use GROUP BY to achieve aggregate values. In your case your are looking for SUM.

Try this

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

Upvotes: 2

kjmerf
kjmerf

Reputation: 4345

This is a simple GROUP BY. You want to group the employees by name so specify employeename in the GROUP BY statement. Then just select HOW you want to group the other columns. In this case, you want to SUMthem:

SELECT employeename, 
SUM(weeklyhours) as total_weekly_hours, 
SUM(points_rewarded) as total_points
GROUP BY employeename

Note that you could also use AVG or MIN or MAX in place of SUM depending on what you want to find. The AS clause specifies what you want to call a particular column in your output.

Tested here: http://sqlfiddle.com/#!9/2a96f

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query.

      SELECT EmployeeName
           ,SUM (weeklyHours)Total_weekly_hours
           ,SUM (pointsrewarded) TotalPoints 
       FROM YourTable 
     Group By EmployeeName

Upvotes: 0

Related Questions