June 1, 2012 at 10:35 am
Hi,
This isn't a re-post. Same query but different question -
SELECT
--E.AuditKey, --can't use newest key to get term info
E.eecDateOfLastHire,
CmpCompanyCode,
E.EecOrgLvl2,
cmpCompanyName,
E.eecCoID,
E.EecEEID, --stays with person, if listed more then once
E.eecDateOfTermination,
E.eecEmplStatus,
E.eecEmpNo,
E.eecLocation,
E.eecFullTimeorPartTime,
E.eecJobCode,
eepNameLast,
eepNameSuffix,
eepNameFirst,
EepNameMiddle
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecEmplStatus in ('A','L')
AND
EecDateOfTermination is NULL
I'm supposed to group by the:
Year (E.eecDateOfLastHire)
then
Company Code (CmpCompanyCode)
then
Branch Code (E.EecOrgLvl2)
I tried :
GROUP BY E.eecDateOfLastHire,CmpCompanyCode,E.EecOrgLvl2
but that breaks with:
Column 'Company.CmpCompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Ideally it would be great if my results could be grouped by, year (date time field) then company code, then branch code.
June 1, 2012 at 10:45 am
With 500 posts and 1307 visits i would expect you to put some sample data, create table scripts and expected results. Can you provide them?
June 1, 2012 at 10:52 am
Cold coffee -
I didn't think they were required to answer a fairly straight forward question about grouping.
If you guys need more info. let me know.
June 1, 2012 at 11:39 am
krypto69 (6/1/2012)
Cold coffee -I didn't think they were required to answer a fairly straight forward question about grouping.
If you guys need more info. let me know.
It's not totally straightforward - remember that grouping is all about aggregation, so you are throwing some rows away along the way. SQL Server needs to know how to do that.
If you group by column 1, 2, 3 and want to include column 4, but column 4 has several values for the various combinations of 1, 2 and 3, how can SQL Server know which of the values to return? It can't return them all, because then the grouping would not be a grouping ...
One thing you can do, to make the query run at least, is to take the MAX() of all the non-grouped columns. But is that what you want?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2012 at 12:11 pm
Your GROUP BY looks okay, but I don't see ANY aggregates in the SELECT list.
June 1, 2012 at 12:59 pm
Yes there are no aggregates...
Hmm...
So I'm not sure how to proceed now.
What I really need is everything grouped by year first, then company code, then branch code. But retaining the row integrity.
Sorry I see what you mean now, it isn't straightforward. My apologies.
What are my options?
June 1, 2012 at 1:02 pm
krypto69 (6/1/2012)
Yes there are no aggregates...Hmm...
So I'm not sure how to proceed now.
What I really need is everything grouped by year first, then company code, then branch code. But retaining the row integrity.
Sorry I see what you mean now, it isn't straightforward. My apologies.
What are my options?
Use ORDER BY instead of GROUP BY and/or use a reporting tool to format the output as you require.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2012 at 2:12 pm
Okay will do.
Thanks Phil and everyone else.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply