Avoid Duplicate Email in Account on Update : Apex

Question

In my Salesforce they are multiple accounts which currently have null value in their Email field. I am trying to figure out a way so that duplicate Email-ids are not entered. My current code is including the current row as well as I guess. So when I press save to update email of an account(which I am sure is distinct), I get email already exist message.

UPDATE ---------- SOLUTION

 integer count= [SELECT COUNT() FROM Account WHERE Email__c =:a.Email__c AND Id !=:a.Id];
        if (count > 0)

QUESTION

How to find the count of email-id except the current row?

My Code

trigger AccountCallout on Account (before update) {
   // make the asynchronous web service callout
for (Account a : Trigger.new) {
      integer count=[select count() from Account where Email__c=:a.Email__c];
        if (count > 0)
          {     
            a.Email__c.adderror('This email already exists');
          }

Thanks for the Answers, I am unable to achieve the same

trigger AccountCallout on Account (before update) {
  // make the asynchronous web service callout
 set<String> emailIds = new set<String>();
   set<Id> idSet = new set<Id>();
for (Account a : Trigger.new) {

// Comment by tushar on 15 sept 2016
// to check email and name are not null
if(a.Name == null || a.Email__c ==null || a.NumberOfEmployees > 32766){   
    if(a.Name == null || a.Email__c ==null)
    {
       a.Email__c.adderror('There was a problem updating the accounts. Email and Account name cannot be blank');
    }
    if(a.NumberOfEmployees > 32767)
    {
        a.NumberOfEmployees.adderror('Number is too large');
    }
}
else
{

  Account OldAccount = Trigger.oldMap.get(a.Id);

  // to make sure email can be changed only when its old value was null
   if((OldAccount.Email__c == null && a.Email__c != null) || OldAccount.Email__c == a.Email__c)    
    {
      // **This is where I am trytinh to avoid duplicacy**
     if(a.Email__c != Trigger.oldMap(a.id).Email__c)
        {               
    // To make sure webservice is called only when there is a change in data
    if(OldAccount.Name != a.Name || OldAccount.BillingStreet != a.BillingStreet)                          
    {
     WebServiceCallout.sendNotification(a.Name,a.Email__c, a.BillingStreet); 
    }   
   }            
    }        
   else
    {
         a.Email__c.adderror('There was a problem updating the accounts. Email cannot be changed');
    }

    }
}
 List<AggregateResult> lst = 
           [SELECT Email__c, COUNT(Id) 
            FROM Account
            WHERE Email__c IN : emailIds
            AND Id NOT IN:idSet
            AND Email__c !=null
            GROUP By Email__c
            HAVING COUNT(Id)>0];

List<String> lstStr = new List<String>();                   
for (AggregateResult ar : lst)  
{
    lstStr.add((String) ar.get('Email__c'));
}
Trigger.new[0].addError(String.join(lstStr,',') + ' already exist');    
}   
}

Answer 1

First, you need to get the query outside of your loop.

Second, you can do this via Declarative

Just set the Email__c field to a Unique field and possibly External Id field if needed.

Then it will let you save if no other Accounts have that email but produce an error if other accounts have that email. Null values are allowed and do not trigger the unique check.

Answer 2

Apart from Eric's suggestion, if you really handle by trigger, then this could be an approach.

Also, make sure SOQL will be outside of for loop.

trigger AccountCallout on Account (before update) 
{
// make the asynchronous web service callout
    set<String> emailIds = new set<String>();
    set<Id> idSet = new set<Id>();

    for (Account a : Trigger.new) 
     {
        if(Trigger.isUpdate)
        {
            if(a.Email__c != Trigger.oldMap(a.id).Email__c)
            {
                emailIds.add(a.Email__c);
                idSet.add(a.id);
            }
        }           
     }

    List<AggregateResult> lst = 
               [SELECT Email__c, COUNT(Id) 
                FROM Account
                WHERE Email__c IN : emailIds
                AND Id NOT IN:idSet
                AND Email__c !=null
                GROUP By Email__c
                HAVING COUNT(Id)>0];

    List<String> lstStr = new List<String>();                   
    for (AggregateResult ar : lst)  {
        lstStr.add((String) ar.get('Email__c'));
    }
    Trigger.new[0].addError(String.join(lstStr,',') + ' already exist');

}