Avatar of ptslv
ptslv
Flag 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 ";
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
ptslv

8/22/2022 - Mon
ptslv

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 ";
Anuradha Goli

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

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!