• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

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

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
g127404
Asked:
g127404
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
g127404Author Commented:
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
 
Patrick MatthewsCommented:
Hi g127404,
> all I need is 1 of those records... not 6.

OK, *which one*?

Regards,

Patrick
0
 
g127404Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Patrick MatthewsCommented:
g127404,

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

Regards,

Patrick
0
 
dduserCommented:
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
 
imran_fastCommented:
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
 
g127404Author Commented:
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
 
g127404Author Commented:
oops, wrong accepted answer... wanted to give it to dduser. sorry, got click happy on the accept button.
0
 
imran_fastCommented:
hi g127404,
Post your comment in community support
http://www.experts-exchange.com/Community_Support/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now