How to group by week even when the count is 0 My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:

My current output:

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344


The result above is missing many weeks (1,2,3 and 15,16,17 etc.) How do i get to show those with 0 count?

TEST EXAMPLE:

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES 
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');


;with ResultsTable as
(

SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A 

WHERE YEAR(A.DATE) = '2019' AND 

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE &lt; '2019-01-01') 
GROUP BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber &lt; (
        SELECT datename(week, '2019-12-31')
        )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber
Could the solution be as simple as using a recursive CTE to generate a numbers table of the weeks in the year and then left joining to your main query?  Using coalesce to output either the real amount or zeros when no match on week number is found?

--demo setup
Declare @ResultsTable table (
  [week] INTEGER,
  amount INTEGER
);

INSERT INTO @ResultsTable
  ([week], amount)
VALUES
  ('4', '354'),
  ('6', '222'),
  ('7', '144'),
  ('8', '354'),
  ('9', '45'),
  ('10', '55'),
  ('11', '76'),
  ('12', '98'),
  ('13', '45'),
  ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber &lt; (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
    ON rt.[week] = wn.WeekNumber




| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |




So, using your original query incorporated into a Common Table Expression, the final solution might look something like this:

;with ResultsTable as
(
SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM 
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE &lt; '2019-01-01') GROUP 
BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 
)
,WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber &lt; (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
    ON rt.[week] = wn.WeekNumber