AnsweredAssumed Answered

SQL count(*) wrong result

Question asked by ariley on Sep 28, 2014
Latest reply on Sep 29, 2014 by ariley

I have the following SQL calculation:


ExecuteSQL (

"SELECT Count(*),

sum ( case when Case_Type='General Liability' then 1 else 0 end ) GLCount,

sum ( case when Case_Type='Auto' then 1 else 0 end ) AutoCount,

sum ( case when Case_Type='Workers Compensation' then 1 else 0 end ) WCCount,

sum ( case when Case_Type='Other' then 1 else 0 end ) OtherCount

FROM charting_case

GROUP BY Intake_Month_Name"

; "," ; ¶




Here's what the data viewer looks like with the result:


The charting_case TO is connected to charting, and the records are constrained to the entire year before the last of this month. This works fine and FileMaker returns the correct data set when checked.


I want to see a comparison from month-to-month. So I need a number for every month for every case type. In the case table I have a calc that is the Intake_Month_Name (returns the month of the date).


Now, when I constrain the records to just show the General Liability for last September, FM gives me 33 records and SQL gives me 385.


So ideally I'd like to have a dataset that looks like this:






where the rows will be the months and the letter would be substituted with the proper count of case types for each. I tried grouping by the Intake_Month_Year field, as well, but then I get even more wacky results.


Can anyone can help me tackle this?