Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jadixon
Starting Member
4 Posts |
 Posted - 2008-07-28 : 15:45:17
|
| I have a query that works perfectly in access, but when I try to run it in sql server, it does not add up the like records as it does in access, so I end up with more records than I should (and therefore there are no totals). Here is my querySELECT TOP (100) PERCENT dbo.Table2.[1] AS EAN, dbo.Query89D_Complete_Labor.WBS, dbo.Table2.[2] AS Emp, SUM(dbo.Query89D_Complete_Labor.ACT) AS ACT, dbo.Query48_Complete_Labor.RegPayRate, dbo.Query48_Complete_Labor.RateThisWeek, dbo.Query48_Complete_Labor.P2E, dbo.Query89D_Complete_Labor.JobFROM dbo.Query48_Complete_Labor INNER JOIN dbo.Query109D RIGHT OUTER JOIN CCRS.dbo.Table46 INNER JOIN dbo.Query89D_Complete_Labor ON CCRS.dbo.Table46.[1] = dbo.Query89D_Complete_Labor.WBS ON dbo.Query109D.WBS = dbo.Query89D_Complete_Labor.WBS ON dbo.Query48_Complete_Labor.[3] = dbo.Query89D_Complete_Labor.[3] INNER JOIN dbo.Table2 ON dbo.Query89D_Complete_Labor.EAN = dbo.Table2.[1] AND dbo.Query48_Complete_Labor.[2] = dbo.Table2.[1]GROUP BY dbo.Table2.[1], dbo.Table2.[2], dbo.Query89D_Complete_Labor.WBS, dbo.Query48_Complete_Labor.RegPayRate, dbo.Query48_Complete_Labor.RateThisWeek, dbo.Query48_Complete_Labor.P2E, dbo.Query89D_Complete_Labor.Job, dbo.Query109D.WBS, dbo.Query109D.SumOfValue, dbo.Query89D_Complete_Labor.ACTHAVING (dbo.Table2.[2] IS NOT NULL) AND (SUM(dbo.Query89D_Complete_Labor.ACT) <> 0) AND (dbo.Query48_Complete_Labor.P2E <> 4 AND dbo.Query48_Complete_Labor.P2E <> 5)ORDER BY EAN, dbo.Query89D_Complete_Labor.WBSWhat should happen is that when the employee has charged to the same WBS, the actual total (called ACT) should be summed. Right now there aren't any records being summed together. Am I doing something wrong? Like I said, this works perfect in access. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-07-28 : 15:58:10
|
| looks like you are SUMing and GROUPing by "dbo.Query89D_Complete_Labor.ACT"If you want to sum that column it should not appear in the GROUP BY clauseBe One with the OptimizerTG |
 |
|
|
jadixon
Starting Member
4 Posts |
Posted - 2008-07-28 : 16:00:51
|
| Thanks! That was it. I am not sure what I was doing wrong earlier, but I had an error that it must be in the group. I have played with it some since then, but never tried removing it (since I previously had an error).Thanks again! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-07-28 : 16:12:55
|
My goodness .. please tell me this is just a sample database you are playing around in, and not a real-live production database with table/view/column names like that!  - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jadixon
Starting Member
4 Posts |
Posted - 2008-07-28 : 16:24:14
|
| Please don't ask..... I inherited some real crap, and I haven't had the time (translate to funding) to make it look like something real. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
SUM function not working as I would expect