Execute SQL, Select Count Subquery

[RNPaul]

 Hello, I have developed a FM database over the last 6 years and this is the first time I have not been able to solve a problem searching the forums. 

Yet seemingly simple, I have not been able to create a working SELECT COUNT () statement (or alternative)  to count the subquery listed below. There is many references to MySQL; however, they fail with FM execute SQL.

Any help would be appreciated      

Let ( [

hospital = Dashboard::g~SQL~Hospital ;
year = Dashboard::g~SQL~Year ;
month = Dashboard::g~SQL~Month ;
$$YearList = Dashboard::g~SQL~Year ] ;

ExecuteSQL ( "SELECT COUNT (b.cEncURN)
	FROM Encounters b
	WHERE b.cInitialHospitalNameShort = ? 
	AND b.InitialCategory = 'Code Blue' 
	AND b.cYear = ? 
	AND b.cMonthName = ? 
	GROUP BY b.cEncURN 
	HAVING COUNT ( b.cEncURN ) > 1"

	; "" ; ¶ ; hospital ; year ; month ) )
    
 Result:
 2
 3
 3
 2
 2
 2
 Wanted Result
 6

 

[bcooney]

drop the SELECT COUNT and wrap the result in ValueCount().

[RNPaul]

Let ( [

hospital = Dashboard::g~SQL~Hospital ;
year = Dashboard::g~SQL~Year ;
month = Dashboard::g~SQL~Month ;
$$YearList = Dashboard::g~SQL~Year ] ;

ExecuteSQL ( 
	"SELECT 
		(SELECT COUNT (b.cEncURN) 
		FROM Encounters b 
		WHERE (Year(b.EncStart)*100+Month(EncStart)) = a.cYearMonth 
		AND b.cInitialHospitalNameShort = ?
		AND b.InitialCategory = 'Code Blue'
		GROUP BY b.cEncURN
		HAVING COUNT (*) > 1)
	FROM Months a 
	WHERE a.cYear = ? 
	GROUP BY a.cYearMonth" 

	; "" ; "" ; hospital ; year ) )

Thanks for the reply bcooney,

I have investigated that option and it works well. However, it can only be used to count an individual month. Unfortunately, the query is used to populate the y value in a chart giving me the totals of each month with 0 values if there is no values for that month (see full query above). 

Obviously the two (2) GROUP BY and HAVING COUNT are my problem. If I take the subquery GROUP BY and HAVING COUNT statements out the query works well. However, only gives me the total Code Blues per month

I am trying to count patients (URN) that have multiple medical emergencies (Code Blue) in a year listed by month with 0 value if null

Edited October 26, 2019 by RNPaul

[bcooney]

I would explore this approach. https://www.geistinteractive.com/2018/04/02/filemaker-summary-table/

[RNPaul]

Thanks bcooney, from a quick look, this might solve my problem.