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,055 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 37
SSMS Opening Mode 9 18
SQL Availablity Groups List 2 7
Help With SQL Query 9 30
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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