MSSQL Query to find Duplicate E-Mail Adresses

Question

Hey everybody,

we have MSSQL and I want to find duplicate E-Mail Adresses. Atlassian offers only this POSTGRESQL Query:

--DUPLICATED EMAIL ADDRESSES - TESTED ON POSTGRESQL
select lower_email_address, count(lower_email_address), array_agg(user_name) as "Users with Dupe E-Mail"
from cwd_user group by lower_email_address having count(lower_email_address) > 1;

-> As array_agg is not a valid function on MSSQL, I want to modify the query to run - but it does not work. Do you have an idea how to solve this?

Thanks a lot!

Hannes

Kishan Sharma · Answer

Right, though that workaround is for JIRA, it should yield the result for confluence as well because of its similar schema.

Benjamin_tmh9yg · Answer

HI @Hannes ,

Perhaps this link might leads you to the right direction:

https://jira.atlassian.com/browse/JRASERVER-9182

-Ben