Add SUM () in the querry results

To do the sum of the value, I linked the two tables through a LEFT JOIN and I searched for SUM(ContractValue) :

DECLARE  @mes  int;  
SET  @mes  =  1;  
WHILE (select @mes) <= 12 
BEGIN  

SELECT LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2) [Codigo]
    , COUNT(*) [Quantidade]
    , SUM(ContractValue) [Valor]
FROM PublicContestCPV
LEFT JOIN PublicContests
    ON PublicContestCPV.PublicContestID = PublicContests.PublicContestID
    AND UEPublicContestType = ''
WHERE YEAR(DateCreated) = '2016' 
    AND MONTH(DateCreated) = (select @mes)
GROUP BY LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2)
ORDER BY LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2)

SET @mes += 1;
END  
PRINT @mes; 

Here is the code proposed in the topic Switch WHILE by GROUP in SQL (very similar to this and both of its authorship), modified to add the summation:

-- código #1 v2
USE CCILCDatabase;  
go

SELECT month(DateCreated) as Mes,
       convert(char(2), CPCCodeID) as Alg2, 
       count(*) as Qtd,
       sum(Contractvalue) as Soma

  from PublicContestCPV

  where DateCreated between '20160101' and '20161231'
        and PublicContestID in (SELECT ContestID 
                                  from PublicContests
                                  where UEPublicContestType = '')

  group by month(DateCreated), convert(char(2), CPCCodeID)

  order by Mes, Alg2;

It's much more efficient to use the GROUP BY clause than the WHILE loop. When using the loop, data is read 12 times whereas with the GROUP BY clause the data is read only once.

(1) To access the contents of the @mes variable, it is not necessary to use the (select @mes) construct. Just use @mes . For example:

WHILE @mes <= 12

(2) Constructs of type where YEAR(DateCreated) = '2016' can be inefficient if there is a coverage index for DateCreated column. Search for sargable .