Jump to content

Execute SQL, Select Count Subquery


This topic is 1616 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

 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

 

Link to comment
Share on other sites

  • Newbies
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 by RNPaul
Link to comment
Share on other sites

This topic is 1616 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.