mysql How to get count of a query's resultant rows with conditions on column returned by datediff

Question

The query that I used (on the right of the link) has generated this result (on left).
Please see http://www.sqlfiddle.com/#!2/f34f1/1

I am new to JasperReports and MySql. I am trying to

count not yet Referred suspects/confirmed clients
count of clients who were referred within 5 days
count of clients who took more than 5 days to get referred.

I am able to get count of clients who were referred within 5 days

SELECT COUNT(*) from 
(select p.patient_id,

(CASE WHEN st.smear_result <> 'NEGATIVE' OR st.gxp_result='MTB+'
THEN IF(DATEDIFF(r.date_referred,MIN(st.date_smear_tested)) IS NULL,'N/A',(DATEDIFF(r.date_referred,MIN(st.date_smear_tested))))

ELSE
(CASE WHEN st.smear_result='NEGATIVE' OR st.gxp_result='MTB-'
THEN IF(DATEDIFF(r.date_referred,MAX(st.date_smear_tested)) IS NULL,'N/A',(DATEDIFF(r.date_referred,MAX(st.date_smear_tested))))

ELSE 'N/A' end )END) as days_taken,

IF(r.date_referred IS NULL,'N/A',r.date_referred) date_referred
from patient as p 
right outer join sputum_test as st on p.patient_id=st.patient_id 
right outer join referral as r on r.patient_id=st.patient_id

where p.suspected_by is not null and (p.patient_status='SUSPECT' or p.patient_status='CONFIRMED')

group by p.patient_id

having days_taken <=5) AS SUBQUERY;

And count of clients who took more than 5 days to get referred.

SELECT COUNT(*) from 
(select p.patient_id,

(CASE WHEN st.smear_result <> 'NEGATIVE' OR st.gxp_result='MTB+'
THEN IF(DATEDIFF(r.date_referred,MIN(st.date_smear_tested)) IS NULL,'N/A',(DATEDIFF(r.date_referred,MIN(st.date_smear_tested))))

ELSE
(CASE WHEN st.smear_result='NEGATIVE' OR st.gxp_result='MTB-'
THEN IF(DATEDIFF(r.date_referred,MAX(st.date_smear_tested)) IS NULL,'N/A',(DATEDIFF(r.date_referred,MAX(st.date_smear_tested))))

ELSE 'N/A' end )END) as days_taken,

IF(r.date_referred IS NULL,'N/A',r.date_referred) date_referred
from patient as p 
right outer join sputum_test as st on p.patient_id=st.patient_id 
right outer join referral as r on r.patient_id=st.patient_id

where p.suspected_by is not null and (p.patient_status='SUSPECT' or p.patient_status='CONFIRMED')
group by p.patient_id
having days_taken > 5) AS SUBQUERY;

But how do I get count not yet Referred suspects/confirmed clients ?

My plan is to somehow get the result as 2 columns:
Column 1 :showing the 3 conditions and Column 2: Showing the sum of rows next to them.

I will pass the solution query in iReport designer to make a pie chart of the 3 conditions as labels and their count showing the percentages for each slice.

score 0 · Answer 1 · answered Aug 30 '13 at 07:28

0

Something like this should work:

SELECT SUM(days_taken <= 5) AS within_5_days,
       SUM(days_taken > 5) AS more_than_5,
       SUM(days_taken IS NULL) as not_yet_referred
FROM (...) AS subquery

Obviously, the subquery should produce NULL for non-referred clients, not N/A as in your original subquery.

answered Aug 30 '13 at 07:28

Barmar

741,623
53
500
612

I tried your solution 'N/A' is being counted in within_5_days and giving a wrong count. The subquery is not producing 'NULL' – user2474410 Aug 30 '13 at 07:41
I said you need to change the subquery to return NULL, not N/A. – Barmar Aug 30 '13 at 07:47

mysql How to get count of a query's resultant rows with conditions on column returned by datediff

1 Answers1