Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,160 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 93

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 93

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 1000 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

885 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