Link to home
Start Free TrialLog in
Avatar of Wm Allen Smith
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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Please provide some sample data/output to clarify your request.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

That said, here's a guess:
select a2, case when count(*) over(partition by a2) =1 then 1 else 0 end
from some_table

Open in new window


Example here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=eea8241192f42556834bc679aec6a568
Let see your formula:
User generated image
This formula allows you to find duplicates and keep only 1 of them.
Is this what you want?
Avatar of Wm Allen Smith

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial