This Works in FoxPro?

  • 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!


  • 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


    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



    * Noel

  • when using aggregate functions, you need to have the non-aggregated columns in the "group by" clause

    Kindest Regards,

    Seth Washeck

    If nothing in this world satisfies me, perhaps it is because I was made for another world.
    C. S. Lewis

  • 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