Oh the reason on the group by is because I’m trying to debug queries that are giving me inflated results.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Monday, March 03, 2014 5:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] sql difference on "sum"
Pros, I’m trying to do a simple sum and am getting some strange results. Why does this total sum up like it does?
SELECT CompletionsTucson.EmployeeNumber, CourseHistoryTucson.Duration, Left([SkillRequired],3) AS Expr1
FROM EmployeesTucson INNER JOIN ((CourseHistoryTucson INNER JOIN CompletionsTucson ON CourseHistoryTucson.CourseHistID = CompletionsTucson.CourseHistID) INNER JOIN EmployeeRequirementsTucson ON CompletionsTucson.EmployeeNumber = EmployeeRequirementsTucson.EmployeeNumber) ON EmployeesTucson.EmployeeNumber = CompletionsTucson.EmployeeNumber
GROUP BY CompletionsTucson.EmployeeNumber, CourseHistoryTucson.Duration, Left([SkillRequired],3), CompletionsTucson.CompleteDate
HAVING (((CompletionsTucson.EmployeeNumber)=18702) AND ((Left([SkillRequired],3))="AMT") AND ((CompletionsTucson.CompleteDate)>#12/31/2013#));
Giving results:
| Query1 | ||
| EmployeeNumber | Duration | Expr1 |
| 18702 | 0.75 | AMT |
| 18702 | 1 | AMT |
| 18702 | 1 | AMT |
| 18702 | 1.5 | AMT |
To something I’m expecting for a 4.25: of
SELECT CompletionsTucson.EmployeeNumber, Sum(CourseHistoryTucson.Duration) AS SumOfDuration, Left([SkillRequired],3) AS Expr1
FROM EmployeesTucson INNER JOIN ((CourseHistoryTucson INNER JOIN CompletionsTucson ON CourseHistoryTucson.CourseHistID = CompletionsTucson.CourseHistID) INNER JOIN EmployeeRequirementsTucson ON CompletionsTucson.EmployeeNumber = EmployeeRequirementsTucson.EmployeeNumber) ON EmployeesTucson.EmployeeNumber = CompletionsTucson.EmployeeNumber
GROUP BY CompletionsTucson.EmployeeNumber, Left([SkillRequired],3), CompletionsTucson.CompleteDate
HAVING (((CompletionsTucson.EmployeeNumber)=18702) AND ((Left([SkillRequired],3))="AMT") AND ((CompletionsTucson.CompleteDate)>#12/31/2013#));
| Query1 | ||
| EmployeeNumber | SumOfDuration | Expr1 |
| 18702 | 2 | AMT |
| 18702 | 2 | AMT |
| 18702 | 1.5 | AMT |
| 18702 | 3 | AMT |
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
beaerospace.com
Passion to Innovate. Power to Deliver
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar