LFreehauf
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).