SQL multiple SUM with conditions in a single query

Working on a Laravel application, I need to retrieve data from a second database, which is why I have a special "DB" like this : DB::connection('sqlsrv2'). On this database, there's a table customerAccount and a table Opportunity. We want to know how many opportunity we create, win or lose, each month, and for a precise year and a precise customerFamily. I can retrieve the datas with some foreach and a query for each case (opportunity that's new, won or lost) but it ends up being a >12700 queries to do it for every customer and every month.

So I thought about SQL SUM, and that I could do it in a single (looong) query that would create 3 columns (new, won, lost). Then I would repeat the query for every month of the year selected. The thing is, I don't remember how to do it, and I can't find any help since I don't really know what to search for.

Here's my bad attempt to write my query :

$clients = DB::connection('sqlsrv2')->table('customerAccount')
        ->select(DB::connection('sqlsrv2')->raw(
            "SELECT 'customerAccount.Name',
  SUM(case when opportunity.customerAccountId = customerAccount.Id
  and YEAR(opportunity.OpportunityDate) = $year
  and MONTH(opportunity.OpportunityDate) = $month
  then opportunity.OpportunityDate
  else 0
  end) as nbOppNew,
  SUM(case when opportunity.customerAccountId = customerAccount.Id
  and YEAR(opportunity.OpportunityCloseDate) = $year
  and MONTH(opportunity.OpportunityCloseDate) = $month
  and StageProbability = 99
  then opportunity.OpportunityDate
  else 0
  end) as nbOppWon,
  SUM(case when opportunity.customerAccountId = customerAccount.Id
  and YEAR(opportunity.OpportunityCloseDate) = $year
  and MONTH(opportunity.OpportunityCloseDate) = $month
  and StageProbability <= 1
  then opportunity.OpportunityCloseDate
  else 0
  end) as nbOppLost
  FROM
  customerAccount
  INNER JOIN opportunity ON customerAccount.Id = opportunity.customerAccountId
  WHERE
  MainInvoicingAddress_CountryIsoCode = ES
  and familyId = $family
  GROUP BY customerAccount.Id"
        ))
        ->get();

I'm not able to generate a different error than SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SELECT'

What I really want to get is a single array with the client's name, and for each : a second entry named "nbOppNew", with the SUM value in it. And a third for "nbOppWon", then the fourth with "nbOppLost".

via Chebli Mohamed