January 15, 2009 at 2:12 am
Does anyone know why this sql query doesnt work: incorrect synthax near group by
SELECTS.SubSectionTitle,A.SubsectionID,A.QuestionID, A.ResultValue
FROMAuditResults A
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
WHERE A.SiteAuditID = 4364
ORDER BY S.SubSectionTitle
Group By S.SubSectionTitle
January 15, 2009 at 2:34 am
ORDER BY clause will go after GROUP BY. The query should look like
Group By S.SubSectionTitle
ORDER BY S.SubSectionTitle
Also, you won't be able to select these columns (A.SubsectionID,A.QuestionID, A.ResultValue) as these are not part of group by clause and no aggregate function is used for these columns.
Read about group by on BOL.
What you can do is something like:
SELECT S.SubSectionTitle,A.SubsectionID,A.QuestionID, A.ResultValue
FROM AuditResults A
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
WHERE A.SiteAuditID = 4364
Group By S.SubSectionTitle, A.SubsectionID,A.QuestionID, A.ResultValue
ORDER BY S.SubSectionTitle
-Vikas Bindra
January 15, 2009 at 2:49 am
Thank you very much for your help, I have added an extra column to count the rows per subsection but it returns ones only
SELECTCount(S.SubSectionTitle) Mycount,S.SubSectionTitle,A.SubsectionID,A.QuestionID, A.ResultValue
FROMAuditResults A
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
WHERE A.SiteAuditID = 4364
Group By S.SubSectionTitle,A.SubsectionID,A.QuestionID, A.ResultValue
ORDER BY S.SubSectionTitle
would you know what im doing wrong.
January 15, 2009 at 3:00 am
If you have to count the rows per subsection then you have to remove the extra columns from Select (A.SubsectionID,A.QuestionID, A.ResultValue) and also form the GROUP BY (A.SubsectionID,A.QuestionID, A.ResultValue).
You query will look like:
SELECT Count(S.SubSectionTitle) Mycount,S.SubSectionTitle
FROM AuditResults A
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
WHERE A.SiteAuditID = 4364
Group By S.SubSectionTitle
ORDER BY S.SubSectionTitle
-Vikas Bindra
January 15, 2009 at 3:18 am
i thought its possible to group, and return some usefull infromation as well.
Thank you very much fro your help
January 15, 2009 at 3:33 am
omlac (1/15/2009)
i thought its possible to group, and return some usefull infromation as well
Indeed, its possible to get aggregates without actually grouping the resultset in SQL 2005 using the OVER Clause....for. e.g.
SELECTS.SubSectionTitle,A.SubsectionID,A.QuestionID, A.ResultValue,
COUNT(*) OVER( PARTITION BY S.SubSectionTitle ) AS TotalSubSectionTitles
FROMAuditResults A
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
WHERE A.SiteAuditID = 4364
ORDER BY S.SubSectionTitle
--Ramesh
January 15, 2009 at 3:58 am
If you are not using 2005/08 you can go for below query:
SELECT Result1.Mycount
,Result1.SubsectionID
,S.SubSectionTitle
,A.QuestionID
,A.ResultValue
FROM AuditResults A
INNER JOIN
(
SELECT Count(A.SubSectionID) Mycount,A.SubSectionID AS SubSectionID
FROM AuditResults A
WHERE A.SiteAuditID = 4364
Group By A.SubSectionID
) Result1 ON Result1.SubSectionID = A.SubSectionID
INNER JOIN SubSection S on S.SubSectionID = A.SubSectionID
ORDER BY S.SubSectionTitle
-Vikas Bindra
January 15, 2009 at 5:03 am
thanks very much Vikas
January 15, 2009 at 5:05 am
Thank you very much Ramesh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply