question

poornima.narayanan avatar image
poornima.narayanan asked

display zero count

I have this query: SELECT EMP.EMPR_NAME AS CompanyName , COUNT(DISTINCT PA.PERSON_ADDM2_KEY) AS [TotalforInvoicing] FROM TB_PERSON P ( NOLOCK ) INNER JOIN TB_PERSON_ADDM2 PA ( NOLOCK ) ON PA.PERSON_KEY = P.PERSON_KEY INNER JOIN TB_HB_COACH_REF_TBL_HIST C ( NOLOCK ) ON PA.COACH_ID = C.COACH_ID AND C.INCL_COACH = 1 AND C.HOLDING_COACH = 0 INNER JOIN TB_MBR_ELIG E ( NOLOCK ) ON P.PERSON_KEY = E.PERSON_KEY INNER JOIN TB_EMPR_ENTITY EMP ( NOLOCK ) ON E.EMPR_KEY = EMP.EMPR_ENTITY_KEY AND EMP.EMPR_NAME IN ( 'Pathway Schools', 'FAIRPOINT COMMUNICATIONS', 'Person Directed Supports' ) GROUP BY EMP.EMPR_NAME which shows the below set. I want all the EMPR_NAME to be displayed with counts as zero when no value is returned for EMPR_NAME I used left join instead of inner join. Bt still not working. PLease help ![alt text][1] [1]: /storage/temp/1453-01-1396351965-3.jpg
count
01-1396351965-3.jpg (15.3 KiB)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rsheik avatar image rsheik commented ·
Can u provide some sample data so that i can understand how you joined the tabs.
0 Likes 0 ·
bernard avatar image bernard commented ·
you have to do right join not left join in order to get all EMP.EMPR_NAME
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
In order for this site to work, you need to help us out. Please indicate all helpful answers by clicking on the thumbs up next to them. If any one answer lead to a solution, indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
bernard avatar image
bernard answered
1. if you get the company names with count=null or empty then just do an outer select: Select companyname, case when TotalforInvoicing is null then 0 else TotalforInvoicing end from( .. )a 2. if you do a left joins then the last condition you should put in a "where" clause: where EMP.EMPR_NAME IN ('Pathway Schools','FAIRPOINT COMMUNICATIONS','Person Directed Supports') otherwise it won't really filter it out 3. Count doesn't count NUlls
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered
what about: SELECT EMP.EMPR_NAME AS CompanyName , coalesce(COUNT(DISTINCT PA.PERSON_ADDM2_KEY),0) AS [TotalforInvoicing] ?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.