Syntax Error (missing operator) in query expression

Question

This is the working query

SELECT MWB.Engagement Manager, MWB.ClientName, MWB.EngID, MWB.ServID, MWB.ServName, MWB.Month 2 Expected

FROM W:\B02.xlsx.MWB MWB

WHERE (MWB.Weighting=0.9) AND (MWB.Month 2 Expected<>0.0) AND (MWB.BUDirectorBU = 'Sydney')

ORDER BY MWB.ClientName

I then added the Group By Rollup expression as I needed to sub-total by the column ClientName

SELECT MWB.Engagement Manager, MWB.ClientName, MWB.EngID, MWB.ServID, MWB.ServName, MWB.Month 2 Expected

FROM W:\B02.xlsx.MWB MWB

WHERE (MWB.Weighting=0.9) AND (MWB.Month 2 Expected<>0.0) AND (MWB.BUDirectorBU = 'Sydney')

ORDER BY MWB.ClientName

GROUP BY ROLLUP ('MWB.ClientName')

Could you please tell me what is incorrect with the new additional GROUP BY expression?

Answer 1

Correct.

Standard SQL does not have a means of including sub-totals.

You need ROLLUP for that and ROLLUP is only available in a few dialects of SQL.

As an alternative, just bring the plain data into Excel and then use Excel to add the subtotals (Data / Subtotal) or use a Pivot Table to display the data, including sub-totals.

Answer 2

I tried to do exactly as you wrote and the error disappeared however I didn't get the result that I expected to see.

I was hoping to see a SUBTOTAL by ClientName in an additional row; what I got was all the data sorted in the order of the SELECT columns.

I guess I'm going to have to read up on the UNION query as per your link above.

thanks

Answer 3

With GROUP BY, each field in the SELECT clause either needs to be in an aggregating function (e.g. SUM) or included in the GROUP BY clause.  Looking at your fields the only one that looks likely for aggregation is MWB.Month 2 Expected.  If that is a numeric field that you can SUM then

SELECT MWB.Engagement Manager, MWB.ClientName, MWB.EngID, MWB.ServID, MWB.ServName, SUM(MWB.Month 2 Expected)

FROM W:\B02.xlsx.MWB MWB

WHERE (MWB.Weighting=0.9) AND (MWB.Month 2 Expected<>0.0) AND (MWB.BUDirectorBU = 'Sydney')

GROUP BY MWB.Engagement Manager, MWB.ClientName, MWB.EngID, MWB.ServID, MWB.ServName

ORDER BY MWB.ClientName

However, if none of the fields in the SELECT clause need aggregating you wouldn't need a GROUP BY clause at all.

Answer 4

I am querying an XLSX file within our network.  This XLSX file was created by copying all the lines of a csv into an existing xlsx file so we don't have to keep on re-pointing the query.

I confirmed in the SQL manual that I have for reference and it said that "The GROUP BY clause is used to achieve grouping.  This clause is written after the WHERE and ORDER BY clauses in a SQL statement"

I couldn't find a working group by query so I thought I'd move the group by clause before the ORDER BY clause - I got a different error this time...

The last two lines of the new query

GROUP BY MWB.ClientName

ORDER BY MWB.ClientName

The new error: You tried to execute a query that does not include the specified expression 'Engagement Manager' as part of the aggregate function.

I thought this meant that the ordering of the columns should be

SELECT MWB.ClientName, MWB.Engagement Manager, MWB.EngID, MWB.ServID, MWB.ServName, MWB.Month 2 Expected

I got the same error message "you tried to execute......"

Answer 5

GROUP BY ROLLUP is, as far as I know, a construct that is only available in certain databases, not part of standard SQL.

It would be useful to know (a) the context in which you are using the query and (b) the type of the source database for the data.

If you are not using a source that recognises ROLLUP or are querying via a mechanism that requires standard SQL then you will probably need to use a UNION query.  See, for example,

http://www.compshack.com/sql/oracle-group-rollup

Afterthought: isn't it usual to put the GROUP BY clause before the ORDER BY clause?