My current group by doesn't give me the 3rd row in my example. How do I change my query to add those rows that have no claims as a zero. Put another way, every vendorNumber has to report a HCFA total and a UB total, even if they're zero.
This is flat-out "brute force" to get the job done using only what you've given us... There is almost certainly a better way.
SELECT v.VendorNumber, c.ClaimType, (SELECT Coalesce(Count(*), 0)
FROM ClaimHeader AS ch
WHERE ch.VendorNumber = v.VendorNumber
AND ch.ClaimType = c.ClaimType) AS ClaimCount
FROM (SELECT DISTINCT VendorNumber
FROM ClaimHeader) AS v
CROSS JOIN (SELECT DISTINCT ClaimType
FROM ClaimHeader) AS c