Add SUM() to querry results

Question

I have this querry that gives me the total number of lines that start with the same code

USE CCILCDatabase;  
GO  
DECLARE  @mes  int;  
SET  @mes  =  1;  
WHILE (select @mes) <= 12 
BEGIN  
SELECT LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2), COUNT(*)
FROM PublicContestCPV
where YEAR(DateCreated) = '2016' AND MONTH(DateCreated) = (select @mes) AND (PublicContestID in (SELECT ContestID FROM PublicContests where [UEPublicContestType] = ''))
GROUP BY LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2)
ORDER BY LEFT(CONVERT(VARCHAR(10),[CPVCodeID]),2)
SET @mes += 1;
END  
PRINT @mes;  

I needed, in addition to counting, to show the SUM() one-column (Contractvalue) that’s on the table Publiccontests. My SQL is not very trained, and this querry I need seems impossible.

Answer 1

Here is the code proposed in the topic Exchange WHILE for GROUP in SQL (very similar to this and both of his authorship), modified to add the sum:

-- 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 is much more efficient to use the GROUP BY clause than the WHILE loop. When using the loop, the data is read 12 times while with the GROUP BY clause the data is read only once.

---

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

WHILE @mes <= 12

(2) Constructions of the type where YEAR(DateCreated) = '2016' may be inefficient if there is a hedge index DateCreated. Search for sargable.

Answer 2

To sum up the value, I linked the two tables through a LEFT JOIN and sought 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;