How to group by number of difference days sin SQL query?

I have a final join in my query that joins 2 tables t1 & t2,

select count(t1.customerID) as count, DATEDIFF(day,t2.action_date,min(t1.ordered_date)) as diff_date 
inner join t2
on t1.EMAIL = t2.EMAIL and t1.CATEGORY_ID = t2.CATEGORY_ID  
inner join product on prod.CATEGORY_ID= t1.CATEGORY_ID
where t2.action_date <= t1.ordered_date
group by t2.action_date,t1.CATEGORY_ID

And I get the results like below

Count    diff_date
100        0
222        0
300        0
20         1
40         1

What I want is to get something like this

Count      difff_date
622          0
60           1
and so on...

I am not sure how to get the results like this? Can anyone can help?

Solution

You need another level of aggregation:

SELECT SUM(count) count, diff_date
FROM (
  SELECT COUNT(t1.customerID) count, DATEDIFF(day, t2.action_date, min(t1.ordered_date)) diff_date 
  FROM t1
  INNER JOIN t2 ON t1.EMAIL = t2.EMAIL AND t1.CATEGORY_ID = t2.CATEGORY_ID  
  INNER JOIN product p ON p.CATEGORY_ID = t1.CATEGORY_ID
  WHERE t2.action_date <= t1.ordered_date
  GROUP BY t2.action_date, t1.CATEGORY_ID
) t
GROUP BY diff_date;

Or, use SUM() window function:

SELECT DISTINCT 
       SUM(COUNT(t1.customerID)) OVER (PARTITION BY DATEDIFF(day, t2.action_date, min(t1.ordered_date))) count, 
       DATEDIFF(day, t2.action_date, min(t1.ordered_date)) diff_date 
FROM t1
INNER JOIN t2 ON t1.EMAIL = t2.EMAIL and t1.CATEGORY_ID = t2.CATEGORY_ID  
INNER JOIN product p ON p.CATEGORY_ID = t1.CATEGORY_ID
WHERE t2.action_date <= t1.ordered_date
GROUP BY t2.action_date, t1.CATEGORY_ID;