Cineno28 Cineno28 - 2 months ago 7
SQL Question

SQL, finding a sum on joined tables

I have 3 tables that I'm trying to join, and get the sum of one column. I'm having a lot of difficulty coming up with a working query for it. Here are the tables

CoinAwards
+----+------------+------------+-----------+
| ID | EmployeeID | AwardDate | CoinLevel |
+----+------------+------------+-----------+
| 1 | 116733 | 6/11/2015 | 1 |
| 2 | 116359 | 5/20/2016 | 3 |
| 3 | 116733 | 12/8/2015 | 5 |
| 4 | 10637 | 10/25/2015 | 2 |
| 5 | 105197 | 9/15/2016 | 3 |
+----+------------+------------+-----------+

CoinLevels
+----+-------+
| ID | Value |
+----+-------+
| 1 | 5 |
| 2 | 10 |
| 3 | 15 |
| 4 | 50 |
| 5 | 100 |
+----+-------+

Employees
+------------+------------+----------+
| EmployeeID | FirstName | LastName |
+------------+------------+----------+
| 116733 | John | Doe |
| 116359 | Jason | Watson |
| 10637 | Brandon | Wilson |
| 105197 | Mike | Jones |
+------------+------------+----------+


What I need to do is report the the total coin values given to an employee within a year. So the final result I'm looking for is this:

RESULT: Total Sum of Coin Values given in 2015
+------------+------------+----------+-------------------------+
| EmployeeID | FirstName | LastName | Sum_Of_2015_Coin_Values |
+------------+------------+----------+-------------------------+
| 116733 | John | Doe | 105 |
| 116359 | Jason | Watson | 0 |
| 10637 | Brandon | Wilson | 10 |
| 105197 | Mike | Jones | 0 |
+------------+------------+----------+-------------------------+


Here is the query I've been working on, but it's not working:

SELECT CoinAwards.EmployeeID, CoinAwards.AwardDate, CoinAwards.CoinLevel,
SUM(CoinLevels.Value) AS Sum_Of_2015_Coin_Values,
Employees.FirstName, Employees.Lastname
FROM CoinAwards
LEFT OUTER JOIN
CoinLevels ON CoinAwards.CoinLevel = CoinLevels.ID
LEFT OUTER JOIN
Employees ON Employees.EmployeeID = CoinAwards.EmployeeID
WHERE (CoinAwards.AwardDate BETWEEN '01/01/2015' AND '12/31/2015')
GROUP BY CoinAwards.EmployeeID


Anyone know how I can change this query to get the result I'm looking for? Thanks for any help!

Answer
Select E.EmployeeID
      ,E.FirstName
      ,E.LastName 
      ,Sum_Of_2015_Coin_Values = isnull(sum(L.Value),0)
 From  Employees E
 Left Join  CoinAwards C on (E.EmployeeID = C.EmployeeID  and Year(C.AwardDate)=2015   )
 Left Join  CoinLevels L on (C.CoinLevel = L.ID )
 Group By 
       E.EmployeeID
      ,E.FirstName
      ,E.LastName