Databases
StackOverflow2013
Postsdbo
PONeed ID column to not be distinct when using a Sum aggregate
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed ID column to not be distinct when using a Sum aggregate
    text
    copied!<pre><code>SELECT s.EmployeeID,e.LastName,projectID,sum(s.HoursWorked) AS Total_Hours from TimeSheet s full join Employee e ON e.EmployeeID = s.EmployeeID full Join Project p ON p.ProjectID = e.EmployeeID group by p.ProjectID,s.EmployeeID,e.LastName </code></pre> <p>Which displays this </p> <pre><code>Employeid Lastname projectID Sum(hoursWorked) 4 Peacock NULL 33.00 5 Buchanan NULL 44.00 1 Davolio 1 56.00 2 Fuller 2 41.00 3 Leverling 3 42.00 </code></pre> <p>I want the project ID to show up for the 2 NULLS.</p> <p>UPDATE: this is almost what i need.</p> <pre><code>SELECT COALESCE(et.EmployeeID, p.projectID) AS employeeID , e.LastName , COALESCE(p.projectID, et.EmployeeID) AS projectID , sum(s.HoursWorked) AS Total_Hours from TimeSheet s inner join Employee e ON e.EmployeeID = s.EmployeeID inner join EmployeeTask et on e.EmployeeID = et.EmployeeID inner join Task t On t.TaskID=et.TaskID inner join Project p on p.ProjectID=t.ProjectID group by p.ProjectID, et.EmployeeID, e.LastName </code></pre> <p>This produces </p> <pre><code>Employeid Lastname projectID Sum(hoursWorked) 1 Davolio 1 112.00 2 Fuller 1 82.00 3 Leverling 2 84.00 4 Peacock 2 66.00 5 Buchanan 3 88.00 </code></pre> <p>WHIch lead me to this link <a href="https://stackoverflow.com/questions/2303126/using-multiple-joins-sum-producing-wrong-value">Using multiple JOINS. SUM() producing wrong value</a></p> <p>UPDATE ANSWER: which then i rewrote my sql to get the right answer. </p> <pre><code>SELECT COALESCE(et.EmployeeID, p.projectID) AS employeeID , COALESCE(p.projectID, et.EmployeeID) AS projectID , e.LastName , (Select sum(HoursWorked) FROm TimeSheet WHere TimeSheet.EmployeeID=e.EmployeeID )AS Total_Hours from TimeSheet s inner join Employee e ON e.EmployeeID = s.EmployeeID inner join EmployeeTask et on e.EmployeeID = et.EmployeeID inner join Task t On t.TaskID=et.TaskID inner join Project p on p.ProjectID=t.ProjectID group by p.ProjectID, et.EmployeeID, s.EmployeeID,e.EmployeeID, e.LastName </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload