AnsweredAssumed Answered

Why doesn't Group By work in an SQL statement?

Question asked by dsimonsays on Feb 13, 2019
Latest reply on Feb 15, 2019 by dsimonsays

This SQL statement works, returning the desired data set in the right order:

 

ExecuteSQL("

SELECT Facility_number

FROM targeted_subset

WHERE key_NPI_Number>0

ORDER BY Facility_name ASC

"; "~"; "|")

 

This SQL statement returns only a sad little confused question mark:

 

ExecuteSQL("

SELECT Facility_number

FROM targeted_subset

WHERE key_NPI_Number>0

ORDER BY Facility_name ASC

GROUP BY Facility_name

"; "~"; "|")

 

My first attempt was:

 

ExecuteSQL("

SELECT Facility_number

FROM targeted_subset

WHERE key_NPI_Number>0

GROUP BY Facility_name

"; "~"; "|")

 

Because I really don't care about the order, but that resulted in the question mark, too, so I researched and found someone who claimed Filemaker needed to have things in a certain order before it could accept a Group By command, so I tried incorporating the ORDER BY. It still didn't work, so then I started reducing the complexity of the statement to see if I could get anything close to working, and was able to get ORDER BY without the GROUP BY working - same field - everything else referenced the same. So what's with GROUP BY? skywillmott? You as up on your SQL as you are on your CURL? This makes no logical sense to me, and I'm really frustrated.

Outcomes