Division of tables with Count and Sum

Navigation
0

I'm working on a query where I have some sums and counts, and in some places I need to divide it into a table that I added or counted, for example: 

COUNT(M.ID_MAILING),
SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END)

I need to divide these two operations, so I tried the following: 

(SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) / COUNT(M.ID_MAILING))

(also tried without the "()" more), but the column always returns "0".

What may be missing? AVG I think it does not help me, because it brings up the average of the table and in that case the operations are dealing with different tables.

I'm using SQL Server Management Studio.

Follow the structure of the tables:

Table: Mailing

Table:Status(IS_APPROACH)

Complete Query: 

    SET LANGUAGE 'ENGLISH'

SELECT
    C.NM_CAMPAIGN AS 'Campanha',
    COUNT(M.ID_MAILING) AS 'Base Recebida',
    SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) AS 'Abordagem',
    SUM(CASE WHEN SS.ID_STATUS IN ('1002','1004','1019','1079','1084','1102','1138','1139','1140','1141','1142','1143','1144','1145','1150','1154') THEN 1 ELSE 0 END) AS 'Promessas de Pagamento',
    SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) / COUNT(M.ID_MAILING) AS '% Mailing Abordado',
    SUM(CASE WHEN SS.ID_STATUS_GROUP IN (17,18,19,20,22,23,25,26,30,31) THEN 1 ELSE 0 END) AS 'Contato Efetivo',
FROM MAILING M
    JOIN CAMPAIGN C         ON C.ID_CAMPAIGN = M.ID_CAMPAIGN
    JOIN STATUS SS          ON SS.ID_STATUS = M.ID_STATUS
GROUP BY C.NM_CAMPAIGN
    
asked by anonymous 18.08.2017 / 12:48

1 answer

1

The problem is that the split is returning a broken value, and SQL does not convert to DECIMAL automatically, so you need to convert the values like this: 

CAST(((CAST(SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) AS DECIMAL) / CAST(COUNT(M.ID_MAILING) AS DECIMAL)) * 100) AS DECIMAL(18,2)) AS '% Mailing Abordado'

In this way, it returns the percentage value already formatted with two decimal places. If you want already in the string, formatted for percentage, do: 

REPLACE(CAST(CAST(((CAST(SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) AS DECIMAL) / CAST(COUNT(M.ID_MAILING) AS DECIMAL)) * 100) AS DECIMAL(18,2)) AS VARCHAR) + ' %', '.', ',') AS '% Mailing Abordado'

I hope I have helped.

Hugs!

    
18.08.2017 / 14:40
Running Java Webservice out of eclipse with Maven In savechages says column is null and has value