Have query return a 0 rather than nothing (1 Viewer)

[Mike Hughes]

I’m trying to get the number of cases with an establishment type P and a case type A for each district (DO). The problem is if any district does not have any cases with establishment type P and case type A the query just jumps over that district. It doesn’t list it the district showing it as 0 for cases.

Below is the query. Could someone tell me how to change the query so that if the district has no cases with establishment type P and case type A it returns a 0 for that district.

Thanks MIke

SELECT
OA.DO,
NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE AS P,
NOLDBA_INT_CASE_STATUS.CASE_TYPE AS A,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS CASES INTO PA

FROM NOLDBA_INT_CASE_STATUS RIGHT JOIN OA ON NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE = OA.DO

GROUP BY OA.DO, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.CASE_STATUS

HAVING (((NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE)="P") AND ((NOLDBA_INT_CASE_STATUS.CASE_TYPE)="A") AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O"));

 

[plog]

You need a LEFT JOIN query from a datasource that lists all your district. Do you have that?

 

[Mike Hughes]

I thought I did. But it looks like a right join. should this be left join?
FROM NOLDBA_INT_CASE_STATUS RIGHT JOIN OA ON NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE = OA.DO

OA.DO has all the districts

 

[plog]

RIGHT/LEFT JOINS are really the same thing--all data from one data source, only matching data in another data source.

However, doing that with the query you have will screw things up because you have criteria applied to the NOLDBA_INT_CASE_STATUS table. What I would do is create a query just on NOLDBA_INT_CASE_STATUS to first calculate your totals, then do the left join on that query to OA.

The sub-query will look like this:

SELECT IV_D_D__CODE, ESTABLISH_TYPE AS P, CASE_TYPE AS A,  COUNT(CASE_ID) AS CASES
FROM NOLDBA_INT_CASE_STATUS
GROUP BY  IV_D_D__CODE, ESTABLISH_TYPE, CASE_TYPE, CASE_STATUS
HAVING (((ESTABLISH_TYPE)="P") AND ((CASE_TYPE)="A") AND ((CASE_STATUS)="O"));

Name that something like 'sub_CaseTotals'. Then create a new aggregate query based on that and the OA table, using a LEFT JOIN from the OA table. Bring in the DO field, P, A and Sum on Cases.

 

[Mike Hughes]

I'll give that a try as soon as I can. Thanks! Mike

 

[Mike Hughes]

No that didn't work either. It didn't return the districts where there were no cases with Establishment type P and Case type A

SELECT Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS CountOfCASE_ID, NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE
FROM NOLDBA_INT_CASE_STATUS
GROUP BY NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.CASE_STATUS
HAVING (((NOLDBA_INT_CASE_STATUS.CASE_TYPE)="A") AND ((NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE)="P") AND ((NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE) In ('AA','BB','CC','DD','FF','GG','HH','II','JJ','KK','LL','MM','NN','SS','UU','VV','WW','ZZ')) AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O"));

 

[plog]

Nor should it. That's just the sub-query.

Name that something like 'sub_CaseTotals'. Then create a new aggregate query based on that and the OA table, using a LEFT JOIN from the OA table. Bring in the DO field, P, A and Sum on Cases

Now use the SQL you posted in the query I described above.

 

[Mike Hughes]

I guess I don't understand what you're telling me to do. I don't follow it.. MIke

 

[plog]

So the query you last posted works, except it doesn't show those records without data, right? Save that query calling it 'sub_CaseTotals'.

Now create a new query and bring in sub_CaseTotals and your OA table. Do a LEFT JOIN from OA to sub_CaseTotals. Bring down the districts from the OA table and then the totals from the sub_CaseTotals. Make it an aggregate query and SUM the sub_CaseTotals fields.

 

[Mike Hughes]

SELECT OA.DO, sub_CaseTotals.CountOfCASE_ID, sub_CaseTotals.CASE_TYPE, sub_CaseTotals.ESTABLISH_TYPE
FROM sub_CaseTotals RIGHT JOIN OA ON sub_CaseTotals.IV_D_DO_CODE = OA.DO;

That worked!! Thanks.
One more thing is there a way to put 0 in the field where a district has no cases and can it be done within this query?

 

[plog]

Make it an aggregate query and SUM the sub_CaseTotals fields.

Its like you miss the last sentence I type every time. This time, I have a work around for that.

With the SQL you posted above, make it an aggregate query (click on the Sigma sign in the ribbon) All the fields you brought down will have 'Group By' beneath them. Change the 'Group By' underneath CountOfCase_ID to 'Sum'. Run that and it should provide you with what you need.

This sentence is here to prevent further omissions.

 

[Mike Hughes]

I got it, thank you for all your help! Mike