CODEWITHSUNDEEP
sqlt-sql
Node17
Node17

Reputation: 537

Summing one tables value and grouping it with a single value of another table

I have two tables.

Table 1: Actuals

A table with all the employess and how many hours they have worked in a given month

|  ID  |  empID  |  hours  |  Month  |
--------------------------------------

Table 2:

A target table which has hour target per month. The target refers to an amount that the sum of all the hours worked by the employees should meet.

|  ID  |  hours target  |  Month  |
-----------------------------------

Is it possible to return the sum of all table 1's hours with the target of table 2 and group it by the month and return it in a single data set?

Example

|  Month | Actual Hours | hours target  | 
-----------------------------------------
|    1   |      320     |      350      |

etc.

Hope that is clear enough and many thanks for considering the question.

Upvotes: 1

Views: 72

Answers (2)

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

This should work:


SELECT t.[month], sum(a.[hours]) as ActualHours, t.[hourstarget]
FROM [TargetTable] t
JOIN [ActualsTable] a on t.[month] = a.[month]
GROUP BY t.[month], t.[hourstarget]

Written in plain English, you're saying "give me the sum of all hours accrued, grouped by the month (and also include the target hours for that month)".

Upvotes: 2

user736893
user736893

Reputation: 

WITH 
    t1 AS (SELECT mnth, targetHours FROM tblTargetHours),
    t2 AS (SELECT mnth, sum(hours) AS totalhours FROM tblEmployeeHours GROUP BY mnth)
SELECT t1.mnth, t2.totalhours, t1.targethours
FROM t1, t2
WHERE t1.mnth = t2.mnth

results:

 mnth   totalhours  targethours
 1  135 350
 2  154 350
 3  128 350

Upvotes: 2

Related Questions