March 12, 2021 at 6:40 pm
Why is this not working?
Field 'EmpNo' not contained in either an aggregate function or the GroupBy clause
SELECT EmpNo, SUM(Time1) AS TotalTime, Dept, Job, Description, Rework
FROM TimeData2
GROUP BY EmpNo
Steve Anderson
March 12, 2021 at 6:54 pm
Ok, great Tuturial!
https://qa.sqlservercentral.com/articles/postgresql-where-order-by-and-group-by-clauses
SELECT EmpNo, SUM(Time1) AS TotalTime, Dept, Job, Description, Rework
FROM TimeData2
GROUP BY EmpNo, Dept, Job, Description, Rework
Steve Anderson
March 12, 2021 at 9:50 pm
Fun thing you can look into - windowing functions.
For example, if you wanted to SUM Time1 ONLY by the EmpNo (as in your first query) and not by department, job, description, or rework, Windowing Functions are your friend.
Something along the lines of:
SELECT [EmpNo],
SUM([Time1]) OVER (PARTITION BY [EmpNo]) AS [TotalTime],
[Dept],
[Job],
[Description],
[Rework]
FROM [dbo].[TimeData2]
Few things I did differently than you did are to put square brackets ([ and ]) around all of the object names. This is a habit I have as for me it gives me quick "at a glance" view of what is a keyword and what is an object.
The other thing I did was put the schema in front of the table. The reason for this is SQL lets you have multiple tables with the same name as long as they are on different schemas. By excluding the schema, I MAY get the table I want or I may get one from a schema I am not expecting.
The big change I did though was that "OVER (PARTITION BY" part of the code. Basically OVER tells SQL "this is a windowing function". PARTITION BY lets me group the data by 1 or more columns and have that apply to the single column. So for example, you could have "PARTITION BY EmpNo" and call the column "EmpTotalTime" and then have a second column that is the same but change "EmpNo" to "Dept" and could call it "DeptTotalTime".
Windowing functions are fun.
Alternately, if you wanted to avoid windowing functions BUT you wanted the total time grouped by EmpNo and still show everything else, you could do a nested select or a common table expression (CTE). I can provide examples of that if you like too!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2021 at 6:44 pm
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply