whats wrong with this query i am bind a gridview but it give

Danish Habib

11y1k

When binding a gridview query gives syntax error

SELECT SUM(ISNULL(QT.QuestionTarget,0)+ISNULL(QT.QT_FormMen,0)+ISNULL(QT.QT_ForWomen,0)+ISNULL(QT.QT_ForGirl,0)+ISNULL(QT.QT_ForBoy,0)) AS TARGETTOTAL,

QT.QuestionTarget As Target,QT.Question_ID,Q.QuestionText,AN.QTotal As Achieved,QT.QT_ForWomen AS TW,AN.id,QT.QT_FormMen AS TM,AN.QT_ForMen AS AM,AN.QT_ForWomen AS AW,QT.QT_ForGirl AS TG,AN.QT_ForGirl AS AG,QT.QT_ForBoy AS TB,AN.QT_ForBoy AS AB,V.VillageName,AN.CdfPErsonName,QT.District,AN.Year,

AN.Quarter,D.DistrictName ,QU.Quarter_Name,CC.CommunityCodeName,G.Group_Name,Y.Year_Name,AN.Date_Created,

AN.UserTypes,AN.AnswerID,U.IsActive,AN.Status from AnswersNew AN

INNER JOIN QuestionTarget QT ON QT.Question_ID= AN.Question_ID AND QT.Year=AN.Year AND QT.Quarter=AN.Quarter AND AN.DistrictId=QT.District

INNER JOIN Villages V ON V.VlgID=AN.VillagesId

INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter

INNER JOIN Users U ON U.UserId=AN.CDF_ID

INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID

INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes

INNER JOIN Groups G ON G.Group_ID=AN.UserTypes

INNER JOIN Year Y ON Y.YEAR_ID=AN.Year

INNER JOIN Districts D on D.DistrictId=U.DistrictId

WHERE U.IsVisible = 1 '

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra11y ago
Sorry, I didn't see.

You need to use use group by if you want to use SUM function in your query.
Danish Habib11y ago
This is my query .....

SET @Query = 'SELECT SUM(ISNULL(QT.QuestionTarget,0)+ISNULL(QT.QT_FormMen,0)+ISNULL(QT.QT_ForWomen,0)+ISNULL(QT.QT_ForGirl,0)+ISNULL(QT.QT_ForBoy,0)) AS TARGETTOTAL,
QT.QuestionTarget As Target,QT.Question_ID,Q.QuestionText,AN.QTotal As Achieved,QT.QT_ForWomen AS TW,AN.id,QT.QT_FormMen AS TM,AN.QT_ForMen AS AM,AN.QT_ForWomen AS AW,QT.QT_ForGirl AS TG,AN.QT_ForGirl AS AG,QT.QT_ForBoy AS TB,AN.QT_ForBoy AS AB,V.VillageName,AN.CdfPErsonName,QT.District,AN.Year,
AN.Quarter,D.DistrictName ,QU.Quarter_Name,CC.CommunityCodeName,G.Group_Name,Y.Year_Name,AN.Date_Created,
AN.UserTypes,AN.AnswerID,U.IsActive,AN.Status from AnswersNew AN
INNER JOIN QuestionTarget QT ON QT.Question_ID= AN.Question_ID AND QT.Year=AN.Year AND QT.Quarter=AN.Quarter AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
WHERE U.IsVisible = 1 '
when i omit the "SUM(ISNULL(QT.QuestionTarget,0)+ISNULL(QT.QT_FormMen,0)+ISNULL(QT.QT_ForWomen,0)+ISNULL(QT.QT_ForGirl,0)+ISNULL(QT.QT_ForBoy,0)) AS TARGETTOTAL," then it works fine it just create issue with this sum ....function
Manas Mohapatra11y ago
Hi,

Your query looks like almost OK but you are missing table name(AN). See below yellow marked code need to add in your query. Please replace your original table name with tableName.

Because your query is using AN alias name but not defined in query.

AND AN.DistrictId=QT.District
FROM tableName AN
INNER JOIN Villages V ON V.VlgID=AN.VillagesId

If it doesn't resolve your problem please share the exception you are getting.