Solved

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Posted on 2006-10-28
9
1,068 Views
Last Modified: 2010-05-18
I've switched from a foxpro database to an SQL server database.
My application was working fine with this query:

SELECT workorders.[group], timecard.loginname, payperiod.startDate, payperiod.endDate, timecard.timeenter, timecard.wrknum, workorders.workorder, timecard.date,
FROM timecard, payperiod, workorders
WHERE timecard.loginname='#session.edituser#'
and timecard.date BETWEEN payperiod.startDate AND payperiod.endDate
and num=#payperiodnum#
and rtrim(timecard.timeenter) <> '0' and timecard.wrknum = workorders.wrknum
group by workorders.workorder
order by workorders.workorder

and now it gives an error:
Column 'workorders.group' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know why this is happening... it wants me to have each of the selected columns in the group by statement.  However that gives me the wrong thing.

My results should be distinct workorders and all different...  If I use the word distinct I get all the same column names.
So using distinct I get this:
query - Rows: 6
        DATE      ENDDATE      GROUP      LOGINNAME      STARTDATE      TIMEENTER      WORKORDER      WRKNUM
1       2006-10-16 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120
2       2006-10-17 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120
3       2006-10-18 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120
4       2006-10-19 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120
5       2006-10-20 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120
6       2006-10-23 00:00:00.0       2006-10-29 00:00:00.0       Internal - Overhead       mbogdanov       2006-10-16 00:00:00.0       8       64GA - General & Admin       120

As you can see 64GA - General & Admin is mentioned all 6 times.  Those should all be different.

Please help
0
Comment
Question by:g127404
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 4

Author Comment

by:g127404
ID: 17827107
so actually... it's been a while since I've looked at the program... all I need is 1 of those records... not 6.  Otherwise it's great.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17827161
Hi g127404,
> all I need is 1 of those records... not 6.

OK, *which one*?

Regards,

Patrick
0
 
LVL 4

Author Comment

by:g127404
ID: 17827181
any, it's the grouping of the records I'm concerned with...  64GA - General & Admin should only show once.

This is a timecard application that is looping through all the timecard entries for the payperiod (2 weeks).

This particular query needs to show just the distinct workorder that were worked on during that time.
So for instance a user worked:

workorder1 8hrs
workorder1 8hrs
workorder1 8hrs
workorder1 8hrs
workorder1 8hrs
workorder1 8hrs
workorder2 8hrs
workorder2 8hrs
and all of this was done during a certain timeframe which you can see from my where clause.

The result in this example should return 2 records: workorder1 and workorder2
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17827194
g127404,

If you only want those two columns, then why did your original SQL have eight columns in the SELECT clause?

Regards,

Patrick
0
 
LVL 9

Assisted Solution

by:dduser
dduser earned 250 total points
ID: 17827742
Hi G127404,

First of all your error is coming due to you not mentioning workorders.[Group] in Group By Clause, either it should be in Group by Clause or an aggregation function has to be applied to this column i.e. Max, Min etc.

Another thing which are the columns necessary for you to retrieve, what i understand from your question is you want the Person Name and Workorder person worked on following query would do this:-

SELECT workorders.[group], timecard.loginname,workorders.workorder
FROM timecard, payperiod, workorders
WHERE timecard.loginname='#session.edituser#'
and timecard.date BETWEEN payperiod.startDate AND payperiod.endDate
and num=#payperiodnum#
and rtrim(timecard.timeenter) <> '0' and timecard.wrknum = workorders.wrknum
group by workorders.[Group],workorders.workorder,timecard.loginname
order by workorders.workorder
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17828428
Try using min or max function

SELECT  min(workorders.[group]),
      min(timecard.loginname),
      min(payperiod.startDate),
      min(payperiod.endDate),
      min(timecard.timeenter),
      timecard.wrknum,
      workorders.workorder
FROM timecard, payperiod, workorders
WHERE timecard.loginname='#session.edituser#'
and timecard.date BETWEEN payperiod.startDate AND payperiod.endDate
and num=#payperiodnum#
and rtrim(timecard.timeenter) <> '0' and timecard.wrknum = workorders.wrknum
group by workorders.workorder, timecard.wrknum
order by workorders.workorder
0
 
LVL 4

Author Comment

by:g127404
ID: 17837340
matthewspatrick "If you only want those two columns, then why did your original SQL have eight columns in the SELECT clause?"
Because even though they weren't shown on the application, other queries relied on those columns.  I'm sure I could take away a few but I wanted a way to include those in the query even though I wasn't grouping on them.

dduser, "First of all your error is coming due to you not mentioning workorders.[Group] in Group By Clause"
yes as stated in my original post... I know why the error is happening, I needed a solution around it.
Thank you for your solution.

I haven't tried your solution imran_fast but I've seen something similar to that on other forums and I'm sure that would have given similar results.

Thank you all for your help.
0
 
LVL 4

Author Comment

by:g127404
ID: 17837352
oops, wrong accepted answer... wanted to give it to dduser. sorry, got click happy on the accept button.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17840103
hi g127404,
Post your comment in community support
http://www.experts-exchange.com/Community_Support/
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question