Add SUM () in the querry results

0

I have this querry that gives me the total nr of rows 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 the count, to also show the% w / w of a column ( ContractValue ) that is in the PublicContests table. My SQL is not very trained, and this querry I need seems to me impossible.

    
asked by anonymous 02.02.2018 / 18:45

2 answers

0

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; 
    
02.02.2018 / 18:58
1

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 .

    
02.02.2018 / 20:48