SQL Server Calculating percentages with COUNT [duplicate]

hfwmuf9z 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(104)

This question already has answers here:

How to get a float result by dividing two integer values using T-SQL? (10 answers)
Closed 28 days ago.

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

sql-server

来源：https://stackoverflow.com/questions/76614518/calculating-percentages-with-count

1条答案

按热度按时间

wydwbb8l1#

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]

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Calculating percentages with COUNT [duplicate]

1条答案

相关问题

热门标签

最新问答