Wm Allen Smith

asked on

# Countif Function in t-SQL or Power BI

Countif Function in t-SQL

Is there a way to mimic this excel formula in Sql:

(COUNTIF($A$2:$A2,$A2)=1)+0

This formula starts at row A2 and fills down to the end of the range, counting the occurrences of the value in the previous row. If a value occurs once, then the count is 1, else 0. Can this be done in SQL or Power BI?

sample.xlsx

Is there a way to mimic this excel formula in Sql:

(COUNTIF($A$2:$A2,$A2)=1)+

This formula starts at row A2 and fills down to the end of the range, counting the occurrences of the value in the previous row. If a value occurs once, then the count is 1, else 0. Can this be done in SQL or Power BI?

sample.xlsx

Please provide some sample data/output to clarify your request.

Yes, without actual table data and expected results it will be hard without a lot of guessing.

That said, here's a guess:

Example here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=eea8241192f42556834bc679aec6a568

That said, here's a guess:

```
select a2, case when count(*) over(partition by a2) =1 then 1 else 0 end
from some_table
```

Example here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=eea8241192f42556834bc679aec6a568

ASKER

Thank you for responses. For clarity I have attached a sample file: sample.xlsx. Ideally, the solution should be in SQL as the actual file may have close to 200 rows. I believe that attempting an excel or Power BI solution may be a bit of a resource hog.

Thanks again.

Thanks again.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.