Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unhappy Unanswered: What is wrong with this sql statement

    I need to do a count of the number of lines of record returned from the sub query below. However the server keeps giving an error something like this
    syntax error FROM CLAUSE. pls help me as it is urgent.

    SELECT COUNT(*) AS DP FROM
    (SELECT MOBIX.FROM_COSG_ID, MOBIX.TO_COSG_ID
    FROM Mobix
    WHERE (((Mobix.CONSG_DO_NO)=[testing]))
    GROUP BY Mobix.FROM_COSG_ID, Mobix.To_COSG_ID);

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, this one is easy. You have to give your subquery an alias name, (and also, clean up (some) of those (superfluous) parenthesis) and your code should run fine.

    SELECT COUNT(*) AS DP FROM
    (SELECT MOBIX.FROM_COSG_ID, MOBIX.TO_COSG_ID
    FROM Mobix
    WHERE Mobix.CONSG_DO_NO=[testing]
    GROUP BY Mobix.FROM_COSG_ID, Mobix.To_COSG_ID) as SuperfluousSubqueryName;

    ...and actually, since your aren't doing any aggregates calculations you can use the DISTINCT clause instead of GROUP BY:

    SELECT COUNT(*) AS DP FROM
    (SELECT DISTINCT MOBIX.FROM_COSG_ID, MOBIX.TO_COSG_ID
    FROM Mobix
    WHERE Mobix.CONSG_DO_NO=[testing]) as SuperfluousSubqueryName;
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    thx alot blindman, u really save my hide

Posting Permissions

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