I made a sales list for the top three people that bought products with an amount. I grouped by name and used the sum feature for total sales. When I create a report for that query I get all the names and get a syntax error if I try to sort by the sum of the total sales. Any way around this to only get the top three sales amounts in a report?
Please add specific LO version, OS, database used and the SQL for the query.
Edit your question to add the information or
add a comment. Do not use an answer for this.
With your request you should do all the prep in the Query. You have already gotten the sum, so you just need to add the sequence and limit the records output. This will work in HSQLDB and Firebird embedded:
Select "YOUR_FIELDS", Sum("YOUR_SALES") AS "Total" from "YOUR_TABLE" group by "SALES_PERSON" order by "Total" desc limit 3
The summed field is given the alias of “Total” and using that the sequence is set with
desc - descending order. Finally
limit 3 only selects the first three records as output to the query giving the top three in sales.
This can then be used as input to your report.
If still having problems (and with future questions) please provide additional information as asked for in the comment.
thank you! I was able to get it with your help.