CODEWITHSUNDEEP
sqlsql-server
Cineno28
Cineno28

Reputation: 909

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!

Upvotes: 2

Views: 91

Answers (6)

Nebi
Nebi

Reputation: 326

You need to either use a aggregate function on the other columns as well:

SELECT CoinAwards.EmployeeID, MAX(CoinAwards.AwardDate), MAX(CoinAwards.CoinLevel),
  SUM(CoinLevels.Value) AS Sum_Of_2015_Coin_Values, 
  MAX(Employees.FirstName), MAX(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

or leave them out:

SELECT CoinAwards.EmployeeID, 
  SUM(CoinLevels.Value) AS Sum_Of_2015_Coin_Values
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

OR group by them as well:

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, CoinAwards.AwardDate, CoinAwards.CoinLevel, Employees.FirstName, Employees.Lastname

The Error could be as following:

Column 'dbo.YourTable.col2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I hope this helps you.

Upvotes: 0

Srinivasan Mk
Srinivasan Mk

Reputation: 1

;with cte as ( select employeeid,sum(value) coin_value from CoinAwards ca join CoinLevels cl on cl.id=ca.coinlevel where year(convert(date,AwardDate,107))=2015 group by employeeid ) select employeeid,firstname,lastname,coin_value from cte c join employees e on c.employeeid=e.employeeid

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

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

Upvotes: 6

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can use this as well:

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

Upvotes: 0

Cato
Cato

Reputation: 3701

try something like

SELECT Employees.EmployeeID, Employees.FirstName, Employees.Lastname
SUM(COALESCE(CoinLevels.Value,0)) AS Sum_Of_2015_Coin_Values

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 Employees.EmployeeID, Employees.FirstName, Employees.Lastname

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Remove CoinAwards.AwardDate, CoinAwards.CoinLevel from your SELECT list.

Add Employees.FirstName, Employees.Lastname to your GROUP BY.

Upvotes: 0

Related Questions