Link to home
Start Free TrialLog in
Avatar of ptslv
ptslvFlag for United States of America

asked on

Syntax Error in Union Query

Can anyone tell me why I am getting a message:  "Syntax error ')' near GROUP BY "?

My output should look like:

TaskID               Formname              Recordcount
    1                    Form 1                            3
    1                    Form 2                            1
    2                    Form 1                            4
    2                    Form 2                            0

Here is my query:

" SELECT   MsnID, RecordCount, Formname " +
                        " FROM " +
                        " (SELECT   TaskID, Count(RecNum) as RecordCount, 'Form 1'  AS Formname " +
                        "  FROM   tbl_Table1 " +
                        " UNION " +
                        " SELECT  TaskID,  Count(RecNum) as RecordCount, 'Form 2' AS Formname " +
                        " FROM    tbl_Table2) " +
                        " GROUP BY TaskID, Formname ";
Avatar of ptslv
ptslv
Flag of United States of America image

ASKER

Sorry, query is this:

" SELECT   TaskID, RecordCount, Formname " +
                        " FROM " +
                        " (SELECT   TaskID, Count(RecNum) as RecordCount, 'Form 1'  AS Formname " +
                        "  FROM   tbl_Table1 " +
                        " UNION " +
                        " SELECT  TaskID,  Count(RecNum) as RecordCount, 'Form 2' AS Formname " +
                        " FROM    tbl_Table2) " +
                        " GROUP BY TaskID, Formname ";
Before group by add alias to table name[ AS TABLE123 ]
" SELECT   MsnID, RecordCount, Formname " +
                        " FROM " +
                        " (SELECT   TaskID, Count(RecNum) as RecordCount, 'Form 1'  AS Formname " +
                        "  FROM   tbl_Table1 " +
                        " UNION " +
                        " SELECT  TaskID,  Count(RecNum) as RecordCount, 'Form 2' AS Formname " +
                        " FROM    tbl_Table2)   AS TABLE123  " +
                        " GROUP BY TaskID, Formname ";

Open in new window

Avatar of ptslv

ASKER

I added that and now I get this error:

Column 'tbl_Table1.TaskID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ptslv

ASKER

OK, my query is this:

" SELECT   TaskID, RecordCount, Formname " +
                        " FROM " +
                        " (SELECT   TaskID, Count(RecNum) as RecordCount, 'Form 1'  AS Formname " +
                        "  FROM   tbl_Table1 GROUP BY TaskID" +
                        " UNION " +
                        " SELECT  TaskID,  Count(RecNum) as RecordCount, 'Form 2' AS Formname  GROUP BY TaskID" +
                        " FROM    tbl_Table2 GROUP BY TaskID)  AS  RecordCnts" +
                        " GROUP BY TaskID, Formname ";


I had to add the Group By clause to the Table 1 part as well.

The query finally ran without errors.

Thank you very much!