Exchange WHILE for GROUP in SQL

Question

I need an SQL function to get data grouped by month. I have in the column CPCCodeID codes (ex: 512456). I need to count and group by the first two digits. I need it separated for months (!!!) and to make it easier I made this loop that goes through all the months and groups the way I need it.

At this moment I have arrived at this solution:

USE CCILCDatabase;  
GO  
DECLARE  @mes  int;  
SET  @mes  =  1;  
WHILE (select @mes) <= 12 
BEGIN  
SELECT LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2), COUNT(*)
FROM PublicContestCPC
where YEAR(DateCreated) = '2017' AND MONTH(DateCreated) = (select @mes)
GROUP BY LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2)
ORDER BY LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2)
SET @mes += 1;
END  
PRINT @mes;  

I know you can change this code to not use the loop, but my knowledge of SQL is somewhat limited. I wanted to use this to export to an Excel sheet, but the way it’s implemented doesn’t work.

Answer 1

I need to count and group by the first two digits. I need this separated by months...

Evaluate

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

SELECT month(DateCreated) as Mes,
       convert(char(2), CPCCodeID) as Alg2, 
       count(*) as Estat

  from PublicContestCPC

  where DateCreated between '20170101' and '20171231'

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

  order by Mes, Alg2;

The above code considers that the column DateCreated is the type date and that the column CPCCodeId has at least 2 digits.