Small SQL Query Help

[Justin P.]

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]

Automatic message

This topic has been moved from "FileMaker 13 General Discussion" to "FQL or FileMaker Query Language".

[Kris M]

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.]

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.