Missing Value Grouping

Newbie SQL Question.

I need to build a results grid that reports totals or zero for an attribute

SELECT ClaimType, VendorNumber,
COUNT (ClaimNumber) AS ClaimCount
FROM ClaimHeader
GROUP BY VendorNumber, ClaimType
ORDER BY VendorNumber ASC;

THe underlying data has two claim types (UB, and HCFA). Some vendor numbers have only UB claims or only HCFA claims. I want my totals to show a zero for those vendor numbers. Example

VendorNumber ClaimType ClaimCount
234 UB 21
234 HCFA 12
235 UB 0
235 HCFA 15

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.

Thanks.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-PatP|||Is something else I could provide to help with my question? This might b closer but i'm still missing the zeros for providers with not HCFA or UB claims.

SELECT t.PeriodID, p.ProviderName, p.AltProviderID_1,
t.FormTypeID,
ClaimCount = CASE WHEN SUM(ClaimCount) IS NULL THEN 0 ELSE SUM(ClaimCount) END

FROM dvTATClaim t
INNER JOIN dimProvider p
ON t.ProviderID = p.ProviderID|||Sure...provide the DDL of the table

CREATE TABLE myTable...

Some Sample Data

INSERT INTO myTable(
SELECT 'data' UNION ALL
SELECT 'data' UNION ALL
ect

And what the results should look like...

You could probably use a stop watch to time how long it takes after you post that for your answer...|||Are there other tables that have:
1. Every possible VendorNumber
2. Every possible ClaimType

Also, did the previous query I provided give you the results that you want?

-PatP