Change WHILE by GROUP in SQL

Question

Change WHILE by GROUP in SQL

Navigation

#1 by (0 votes)

0

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 for the first two digits. I need this separated for months (!!!) and to make it easier I did this loop that goes through every month and groups it the way I need it.

At this point I came up with 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 loop , but my SQL knowledge is somewhat limited. I wanted to use this to export to an Excel sheet, but the way it is implemented does not work.

sql sql-server

asked by anonymous 30.01.2018 / 17:38

1 answer

Failed to install / uninstall Netbeans HTML field accepts only the numbers of a Query (SQL-Server)

score 0 · Accepted Answer

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

Rate

-- 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 assumes that the DateCreated column is of type date and that the CPCCodeId column has at least 2 digits.