g127404
asked on
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='#sessi
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
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='#sessi
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
Hi g127404,
> all I need is 1 of those records... not 6.
OK, *which one*?
Regards,
Patrick
> all I need is 1 of those records... not 6.
OK, *which one*?
Regards,
Patrick
ASKER
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
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
g127404,
If you only want those two columns, then why did your original SQL have eight columns in the SELECT clause?
Regards,
Patrick
If you only want those two columns, then why did your original SQL have eight columns in the SELECT clause?
Regards,
Patrick
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
oops, wrong accepted answer... wanted to give it to dduser. sorry, got click happy on the accept button.
hi g127404,
Post your comment in community support
https://www.experts-exchange.com/Community_Support/
Post your comment in community support
https://www.experts-exchange.com/Community_Support/
ASKER