英文:
Calculating percentages with COUNT
问题
我有一个包含列owneridname、createdon和svb_contactname的表，名为incident。我试图使用svb_contactname列来计算每个人的NULL和非NULL值的百分比（PercYes和PercNo列），但我在获取百分比时遇到了问题。
示例数据表如下：
| 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 |
当我尝试使用 COUNT(svb_contactname)/COUNT(*) [PercYes] 时，它似乎只返回1或0（如下面的结果表所示），而我希望返回一个小数作为结果。PercNo列也是同样的情况。
SELECT
owneridname,
CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week],
FORMAT(createdon, 'MMMM', 'en-US') [Month],
COUNT(svb_contactname) AS [Yes], /*计算非NULL值的数量*/
COUNT(*) - COUNT(svb_contactname) AS [No], /*计算列中的NULL值数量*/
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;
初始结果：
| 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 |
我希望生成以下结果：
| 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 |
英文:
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 |
答案1
得分: 0
如果你除以整数，结果将是整数。只需将其中一个变成浮点数，你将得到小数作为结果：
SELECT
owneridname,
CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [周],
FORMAT(createdon, 'MMMM', 'en-US') [月份],
COUNT(svb_contactname) AS [是], /*计算非NULL值的数量*/
COUNT(*) - COUNT(svb_contactname) AS [否], /*仅计算该列中的NULL值数量*/
CAST(COUNT(svb_contactname) AS FLOAT)/COUNT(*) [百分比是],
CAST((COUNT(*)-COUNT(svb_contactname)) AS FLOAT)/COUNT(*) [百分比否]
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
周 DESC;
将这部分改成小数：
ROUND(CAST(COUNT(svb_contactname) AS FLOAT)/COUNT(*) * 100, 2) [百分比是],
ROUND(CAST((COUNT(*)-COUNT(svb_contactname)) AS FLOAT)/COUNT(*) * 100, 2) [百分比否]
英文:
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]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论