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,022 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
  • 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now