aggregate functions with conditions

[Lifeseeker]

Hi there,

I have the following query. I got an error when I ran it.

SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type,
FROM tbl_test
GROUP BY tbl_test.facility_type;

I think the problem is that first WHERE clause inside.

Can anyone help debug?

thanks

 

[boblarson]

The WHERE has to go AFTER the FROM tbl_Test.

 

[plog]

I see what you are getting at. I would use this SQL as a sub-query:

SELECT facility_type AS Type, COUNT(encounter_number) AS TotalRecords, SUM(IIf(status='Death', 1,0)) AS DeathTotal
FROM tlb_test
GROUP BY facility_type;

Name that query 'subDeathRatio'. Then use it as a basis of another query like so:

SELECT Type, DeathTotal/TotalRecords AS DeathRatio
FROM subDeathRatio;