Group and Count Oldest Registries

Question

I have something like this in my database:

Id   CustomerId  ExecutionDate            OfficeId
-------------------------------------------------
1      1         2016-11-17 00:00:00.000  1
2    133         2016-12-23 00:00:00.000  3
3      1         2017-01-27 00:00:00.000  1
4    230         2023-04-11 00:00:00.000  2
5    133         2023-04-12 00:00:00.000  3
6      1         2024-04-13 00:00:00.000  1
7      1         2023-04-17 00:00:00.000  1

I need to count how many customers have One Distinct Execution in each year.

It is the way I can know when that customer was first processed, so I can measure growing by year.

The result must be something like this:

Year    NewCustomers
--------------------------
2016    2
2017    1
2023    3
2024    1

Also, If I need to count how many Offices by year, it would be just do the same logic, just changing the column name?

Year    NewOffices
--------------------------
2016    2
2023    1

I tried this, but didn't work:

SELECT DISTINCT TOP 10 
    YEAR(MIN(ExecutionDate)) 
FROM
    ExecRegistry
GROUP BY
    CustomerId, YEAR(MIN(ExecutionDate))
ORDER BY
    YEAR(MIN(ExecutionDate));

Answer 1

You can use distinct but as part of the count aggregate, such as:

select [Year], Count(distinct CustomerId) NewOffices
from ExecRegistry
cross apply(values(Year(ExecutionDate)))y([Year])
group by [Year]
order by [Year];