Syntax Error in Union Query

ptslv
ptslv used Ask the Experts™
on
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 ";
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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 ";
Anuradha GoliSystems Development / Support Specialist

Commented:
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

Author

Commented:
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.
Database Consultant
Top Expert 2009
Commented:
" 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 " +
                        " FROM    tbl_Table2 GROUP BY TaskID) A" +
                        " GROUP BY TaskID, Formname ";

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial