February 23, 2005 at 4:35 pm
I am converting some FoxPro code into SQL server stored procedures. I don't know FoxPro but managing to read and under the code; most of it is SQL anyway.
One problem I couldn't seem to solve is this piece of code that seems to work in FoxPro but doesn't in SQL server, or need a work around:
SELECT column1, sum(column2), column3
FROM tableA
GROUP BY column1
seems to be working fine in FoxPro but if I do this in SQL server, I get
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tableA.column1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any suggestions on how to work around it? Thanks!
February 24, 2005 at 7:34 am
I had the opportunity to work with Foxpro for a little while and all I can say about its sql capabilities is that until version 6 => it @^&*!
Anyway you could try:
SELECT column1, sum(column2), column3
FROM tableA
GROUP BY column1, column3
OR
SELECT column1, sum(column2), Min(column3)
FROM tableA
GROUP BY column1
I am not really sure what would be the result but you could choose the one that returns the expected results
HTH
* Noel
February 28, 2005 at 3:23 pm
March 1, 2005 at 4:22 am
hi
the error is caused by the fact that you are select column 3, but are not grouping by it.
all non-aggregate columns must be included in the group by clause.
simply changing to
select column1, sum(column2), column3
from tableA
group by column1, column3
will fix your problem
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply