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 |
|
phong919
Starting Member
29 Posts |
 Posted - 2008-08-05 : 12:43:44
|
| hello all, i'm trying to understand why keep getting this group by error from the below sql:SELECT dbo.MSP_PROJECTS.PROJ_ID, dbo.GICAP_Engagement_IDs.ENG_ID, dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_OUTLINE_CODES.OC_NAMEFROM dbo.MSP_CODE_FIELDS INNER JOIN dbo.MSP_PROJECTS ON dbo.MSP_CODE_FIELDS.CODE_CATEGORY = dbo.MSP_PROJECTS.PROJ_ACTUALS_SYNCH INNER JOIN dbo.MSP_OUTLINE_CODES ON dbo.MSP_CODE_FIELDS.CODE_FIELD_ID = dbo.MSP_OUTLINE_CODES.OC_FIELD_ID INNER JOIN dbo.GICAP_Engagement_IDs ON dbo.MSP_PROJECTS.PROJ_ID = dbo.GICAP_Engagement_IDs.PROJ_IDWHERE (dbo.MSP_CODE_FIELDS.CODE_FIELD_ID = 188744592)GROUP BY dbo.MSP_PROJECTS.PROJ_ID, dbo.GICAP_Engagement_IDs.ENG_ID, dbo.MSP_PROJECTS.PROJ_NAMEError Message: Column 'dbo.msp_outline_codes.OC_NAME' is invalid in the select list because it is not contained in either an aggregate function or the group by clause. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-05 : 12:51:48
|
| You are not grouping by dbo.msp_outline_codes.OC_NAME so you cant use it in select list. either include it in GROUP BY or apply an aggregate function on it like MIN or MAX |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-05 : 12:59:12
|
| Grouping means:there may be some more records grouped into one record depending on columns in group by clause.Now your OC_NAME is not in your group by clause and there can be some different values in OC_NAME.If youre sure there is always the same value for all grouped records in OC_NAME then you can use something like max(OC_NAME).Webfred |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-05 : 13:00:01
|
| sorry visakh16.i am too late...Webfred |
|
|
|
phong919
Starting Member
29 Posts |
Posted - 2008-08-05 : 14:11:34
|
| The OC_NAME has 3 values that i'm looking for. Sorry i'm really new at sql, how do you apply the max function to the query? i just need to know which of the 3 value is picked up when a user select one. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-05 : 14:15:15
|
quote:
Originally posted by phong919
The OC_NAME has 3 values that i'm looking for. Sorry i'm really new at sql, how do you apply the max function to the query? i just need to know which of the 3 value is picked up when a user select one.
If you use MAX you will get only the last name as per alphabetical order out of ones per dbo.MSP_PROJECTS.PROJ_ID, dbo.GICAP_Engagement_IDs.ENG_ID, dbo.MSP_PROJECTS.PROJ_NAME combination. wht does your requirement demand? |
|
|
|
phong919
Starting Member
29 Posts |
Posted - 2008-08-05 : 15:40:40
|
| visakh16,Thank you for your reply. My requirements are as follow. the OC_NAME field contains the following values, "2 active", "3 Completed", "1 Not Started","4 On Hold", and "5 Cancelled". So i'm just trying to select one of those valuse base on 1 project name selected. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Group by error