I am learning SQL in M9 to get subtotal of areas (hectares), the subtotals are based on entries in a field in the Table, in this example, grouping is by [ColourThem]. The 3 lines below generate a table subtotalling area (ha) by ColourThem category. I have found reference in literature outside Manifold's M9 user manual to generate a grand total using "WITH ROLLUP". I have tried various variations of WITH ROLLUP putting it between Group and By, by putting it after GROUP BY [ColourThem] and by appending to it WITH ROLLUP [Hectares]. SELECT [ColourThem], Sum([Hectares]) AS Area_ha FROM [My Areas of Interest Drawing Table] GROUP BY [ColourThem] I can generate subtotals using the above then re-write as below to generate a grand total. Can I do it in one step? SELECT Sum([Hectares]) AS Area_ha FROM [My Areas of Interest Drawing Table] The message in the log is "Invalid object reference" as below whenever I try variations of incorporating WITH ROLLUP into the statements: > SELECT [ColourThem], Sum([Hectares]) AS Area_ha FROM [My Areas of Interest Drawing Table] GROUP BY [ColourThem] WITH ROLLUP Invalid object reference.
|