asked on
Group By error (with Subquery)
Experts,
Thank you for taking my question
Objective: I want to return one line grouped by Property (P.scode).
I have specific code (attached) that needs an expert's review.
I'm getting a common error that I can not resolve: "Column table.feild is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I can not seem to resolve, probably due to a subquery and group by function.
Attached find a word document with two T-SQL:
-Group by Tenant - Successful query grouped by a more detailed "Tenant" level
-Group by Property - represents modification to the success query; resulting with the group by error.
I've tried but failed solve. If this question looks familar, it is directly related to an open Crystal question. The Crystal Experts help suggests I attempt fix via T-SQL command (which makes perfect sense, execpt I'm failing to solve vial SQL too)
Data set consists of Many Tenants that belong to a single Property.
Thank you
ExpertExchange-GroupBy-Property.docx
Thank you for taking my question
Objective: I want to return one line grouped by Property (P.scode).
I have specific code (attached) that needs an expert's review.
I'm getting a common error that I can not resolve: "Column table.feild is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I can not seem to resolve, probably due to a subquery and group by function.
Attached find a word document with two T-SQL:
-Group by Tenant - Successful query grouped by a more detailed "Tenant" level
-Group by Property - represents modification to the success query; resulting with the group by error.
I've tried but failed solve. If this question looks familar, it is directly related to an open Crystal question. The Crystal Experts help suggests I attempt fix via T-SQL command (which makes perfect sense, execpt I'm failing to solve vial SQL too)
Data set consists of Many Tenants that belong to a single Property.
Thank you
ExpertExchange-GroupBy-Property.docx
Microsoft SQL Server
Last Comment
jogos
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Group By p.sCode, p.sAddr1, charge.hmy
But see also you use the same subselect from table detail twice. It will be better to make it an extra left join (and then no need to have charge.hmy in group by).