Division of tables with Count and Sum

Asked

Viewed 675 times

0

I am preparing a query where I have some sums and counts and, in some parts, I need to make a division in a table that I have added or made the count, for example:

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

I need to make a division between these two operations, I tried as follows:

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

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

What can be missing? AVG I believe does not serve me, because it brings the average of the table and, in this case, the operations are treating different tables.

I am using SQL Server Management Studio.

Follows the structure of the tables:

Table: Mailing

Tabela: Mailing

Table: Status (IS_APPROACH) Tabela: Status (IS_APPROACH)

Full 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
sql sql-server query

  • What returns SUM and COUNT separately ?

  • @Motta integer values such as 1036 and 301 (first results of these columns).

  • @If you can edit the question and ask how is the structure of your tables doing a favor, I think it would help. I tried to simulate here and I had no problem

  • the structure of the tables and the complete query

  • I edited with the tables and the complete query, @Leandrosimões@rovannlinhalis

  • @Devin I think the problem is that when dividing, you are getting broken values, for example: 1036 / 301 = 3,44186046... so you have to do a CAST of the values for DECIMAL, thus: (CAST(SUM(CASE WHEN SS.IS_APPROACH = 1 THEN 1 ELSE 0 END) AS DECIMAL) / CAST(COUNT(M.ID_MAILING) AS DECIMAL)) AS '% Mailing Abordado' .

  • @Leandrosimões worked!! Another thing, the result was "0.2905405400540545405", for example, I can already leave this at %?

  • @Devin Tries to do 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'. : 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 created an answer, if you can give upvote to help I appreciate! I hope I helped! Need I am available. Hug!

Show 4 more comments

1 answer

1

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

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'

This way already returns the value in percentage formatted with two decimal places. If you want already in 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’ve helped.

Hug!

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.