Get total count by Year

Sorry, but the total number of rows for the year 2021 is 9, not 11.

So, please rephrase your question.

Edit: typo.

Sorry, but the total number of rows for the year 2011 is 9, not 11.

So, please rephrase your question.

There is no 2011 present.

Typo, 2021.

It is 11 ste5an, 9 in MOTREF1 and 2 in MOTREF2 , they are highlighted in yellow to avoid confusion. 

There are 11 occurrences in 9 rows.
7 rows where it occurs once per row. 2 rows where it occurs twice.
Thus 9, not 11.

We are not counting rows, we are counting the number of records per year. It is not relating to name. It is just counting years. 

Records is an object oriented or entity oriented concept. You tagged your question as SQL. There are only rows and columns.

I am trying to use this query in Power BI and it does not accept brackets. I receive a error;
Microsoft SQL: Incorrect syntax near the keyword 'with'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

Any other way of achieving the same results?

Thanks

you may try this:

select yr, count(yr) cnt
from (
   select year(MOTRef1) yr from yourTable union all
   select year(MOTRef2) yr from yourTable union all
   select year(MOTRef3) yr from yourTable
) cte
group by yr
order by yr

Select allOpen in new window

@Ryan, I am posting your solution in a separate question as Power BI needs a simple query without order by and ';'

Thanks, Ryan that worked!

You don't need to scan the table 3 times in order to do this.

Any suggestion? 

Normalize it with CROSS APPLY ( VALUES( ) ).

Can I request to provide an query syntax ?
Thanks ste5an

Thanks Ste5an! Will try and be right back.

Thanks, it worked!