Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    16

    Exclamation Unanswered: FOXPRO / SQL query not working

    Hi. I am trying to run this query that counts duplicates in a table, but it keeps erroring out, saying that the GROUP BY syntax is incorrect:

    SELECT * FROM TABLE1 GROUP BY field1 HAVING COUNT(field1) > 1 WHERE Item = '11111-22222-333'

    Any ideas what I should try instead?

    The exact error message states "SQL: GROUP BY clause is missing or invalid."

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM TABLE1
       WHERE Item = '11111-22222-333'
       GROUP BY field1
       HAVING COUNT(field1) > 1
    Sequence is important, your WHERE clause was out of place!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2002
    Posts
    128
    Provided Answers: 1
    Pat is correct in that the error message is due to your GROUP BY line being in the wrong place within the SQL Query command string for FP/VFP

    But since you are looking for duplicates of Field1 for a given Item, I might suggest a modification to your query

    If you are looking for ALL duplicates

    SELECT Item,;
    Field1,;
    COUNT(Field1) AS Qty;
    FROM TABLE1;
    GROUP BY Item, Field1;
    INTO CURSOR Result

    Then just SCAN/ENDSCAN (or anything else) on the Result cursor FOR Qty > 1

    Or if you were only looking for situations where Item = '11111-22222-333'

    SELECT Item,;
    Field1,;
    COUNT(Field1) AS Qty;
    FROM TABLE1;
    WHERE Item = '11111-22222-333';
    GROUP BY Item, Field1;
    INTO CURSOR Result

    And again just SCAN/ENDSCAN (or anything else) on the Result cursor FOR Qty > 1

    Good Luck

  4. #4
    Join Date
    Jan 2010
    Posts
    16
    Thanks guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •