Justin P. Posted March 13, 2015 Posted March 13, 2015 Cannot get to the bottom of this one... This ExecuteSQL query works fine (all escape characters etc removed for clarity) SELECT a."Month Name", SUM(a."Total") FROM "Orders" a WHERE a."Year" = ? GROUP BY a."Month Name" I have a Month Number field also... Month Number = Month (date) But adding that into the query then kills it completely (returns "?") SELECT a."Month Number", a."Month Name", SUM(a."Total") FROM "Orders" a WHERE a."Year" = ? GROUP BY a."Month Name" I have checked, seems to be appropriate data in all the fields. Any clues? Driving me batty...
Lee Smith Posted March 13, 2015 Posted March 13, 2015 Automatic message This topic has been moved from "FileMaker 13 General Discussion" to "FQL or FileMaker Query Language".
Kris M Posted March 13, 2015 Posted March 13, 2015 Try SELECT a."Month Name", a."Month Number", SUM(a."Total")FROM "Orders" a WHERE a."Year" = ?GROUP BY a."Month Name" , a."Month Number" FileMaker says "columns must match the column expression" on page 14 of the SQL reference.pdf You are selecting two columns and creating one aggregate. All selected columns must be in the GROUP BY clause I would also question your use of a "total" field.. Do you really want the sum of a sum?
Justin P. Posted March 16, 2015 Author Posted March 16, 2015 You nailed it Kris, thanks. In reference to the "sum of a sum", one sum is the invoice total (sum of line items), the aggregate chart is sum of all invoices.
Recommended Posts
This topic is 3792 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now