Calculating percentages with COUNT

Question

I have a table with columns owneridname, createdon,svb_contactname in a table called incident. I am trying to calculate the percentage of NULL and non-NULL values for each person using the svb_contactname column (the PercYes and PercNo column) but I am having trouble getting the percentages to work.

Sample (example) data table:

owneridname	createdon	svb_contactname
Brittany Miller	7/3/2023	NULL
Christine Hak	7/3/2023	Jeremiah Smith
Dawn Cembellin	7/3/2023	Robert Drago
Dominic Sanchez	7/3/2023	Frank Kirby
Dylinn Guiles	7/3/2023	NULL

When I attempt to use COUNT(svb_contactname)/COUNT(*) [PercYes] it only seems to return a 1 or a 0 (as you can see in the results table below) when I am looking for a decimal number to be returned as the result. Same is for the PercNo column.

SELECT
  owneridname,
  CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week],
  FORMAT(createdon, 'MMMM', 'en-US') [Month],
  COUNT(svb_contactname) AS [Yes], /*Counts non-NULL values*/
  COUNT(*) - COUNT(svb_contactname) AS [No], /*Counts only NULL values in the column*/
  COUNT(svb_contactname)/COUNT(*) [PercYes],
  (COUNT(*)-COUNT(svb_contactname))/COUNT(*) [PercNo]
FROM incident
WHERE createdon >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY
  owneridname,
  CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date),
  FORMAT(createdon, 'MMMM', 'en-US')
ORDER BY
  Week DESC;

Initial results:

owneridname	Week Date	Month	Yes	No	PercYes	PercNo
John Smith	7/3/2023	July	7	0	1	0
Margo Johnson	7/3/2023	July	18	7	0	0
Caitlin Dakota	7/3/2023	July	0	2	0	1
Matthew Drake	7/3/2023	July	5	2	0	0
Dan Bingo	7/3/2023	July	0	1	0	1

I am looking to produce these results:

owneridname	Week Date	Month	Yes	No	PercYes	PercNo
John Smith	7/3/2023	July	7	0	1	0
Margo Johnson	7/3/2023	July	18	7	.72	.28
Caitlin Dakota	7/3/2023	July	0	2	0	1
Matthew Drake	7/3/2023	July	5	2	.71	.29
Dan Bingo	7/3/2023	July	0	1	0	1

You are doing integer division, add a decimal expression to resolve eg `COUNT(svb_contactname) * 1.0` — Stu, Jul 04 '23 at 16:45
Considering you want an average, perhaps using `AVG` would be better: `AVG(CASE WHEN svb_contactname IS NULL THEN 0. ELSE 1. END)` — Thom A, Jul 04 '23 at 17:11
@Stu This worked! However how can I reduce the decimal places? I attempted wrapping the functions with ROUND but it didn't seem to work. — hobosapien, Jul 04 '23 at 17:24
`CAST`/`CONVERT` it, @hobosapien . `ROUND`img doesn't change the data type or the precision/scale of a value. — Thom A, Jul 04 '23 at 20:06

Horaciux · Accepted Answer · 2023-07-04T22:05:42.003

If you divide integers, the result is integer. Just by making one of them float, you will get decimal as result:

  SELECT
      owneridname,
      CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week],
      FORMAT(createdon, 'MMMM', 'en-US') [Month],
      COUNT(svb_contactname) AS [Yes], /*Counts non-NULL values*/
      COUNT(*) - COUNT(svb_contactname) AS [No], /*Counts only NULL values in the column*/
      CAST(COUNT(svb_contactname) AS FLOAT)/COUNT(*) [PercYes],
      CAST((COUNT(*)-COUNT(svb_contactname)) AS FLOAT)/COUNT(*) [PercNo]
    FROM incident
    WHERE createdon >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
    GROUP BY
      owneridname,
      CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date),
      FORMAT(createdon, 'MMMM', 'en-US')
    ORDER BY
      Week DESC;

Change this for decimals:

ROUND(CAST(COUNT(svb_contactname) AS FLOAT)/COUNT(*) * 100, 2) [PercYes],
ROUND(CAST((COUNT(*)-COUNT(svb_contactname)) AS FLOAT)/COUNT(*) * 100, 2) [PercNo]

That worked, however how I would keep it to two decimal places? Some of the results are giving 10+ decimals — hobosapien, Jul 04 '23 at 17:20

Calculating percentages with COUNT

1 Answers1