计算百分比 [英] Working Out a Percentage

问题描述

我已经在下面发布了一些脚本，其中包括总计

I have posted some script below which works out the totals

SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

我需要做什么获得第一份工作的百分比，这需要是下表中的额外列表是如何制定的



我犯了一个错误必须是

总的来说，首次就业总数除以总工作次数100 =％



我只需要一些方法来获得百分比因此使用上述方法不使用百分比为什么要求帮助实现这个。

What i need to do is get a percentage for the first jobs, this needs to be an extra colum in the table below is how it needs to be worked out

I have made an error its got to be
total overall 1st Jobs divided by total overall jobs times by 100 = %

I just need some way of getting the percentage using the above method not working with percentage so hence why asking for help on implementing this.

推荐答案

我想我现在理解你了。这里的主要问题是数据类型是整数和可空的，所以如果有空值，返回的结果将为null并且不使用强制转换（浮动）表达式不可交换。



我假设您不需要任何小数位（如果这样做，将所有表达式转换为浮点数）并且总和永远不为空（如果是，则使用ISNULL函数）：



SELECT *

FROM（

SELECT cast（[Id] AS VARCHAR（20））AS ID

，[网站]

，转换（VARCHAR（20），[jobdate]，101）AS [工作日期]

，[submittime]

，[totalfirstjobs]

，[totalSecondJobs]

，[totalThirdjobs]

，[nightwork]

，[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [总计所有工作]

，（ISNULL（[totalfirstjobs]，0）* 100）/（ISNULL （[totalfirstjobs]，0）+ ISNULL（[totalsecondjobs]，0）+ ISNULL（[totalthirdjobs]，0）+ [nightwork]）AS percentfirstjobs

来自site_total

WHERE转换（VARCHAR（20），[jobdate]，101）= convert（VARCHAR（20），getdate（），101）



UNION



SELECT'Totals'

，NULL

，convert（VARCHAR（20），[jobdate] ，101）

，NULL

，总和（totalfirstjobs）

，总和（totalsecondjobs）

，sum（ totalthirdjobs）

，总和（夜间工作）

，总和（totalfirstjobs）+总和（totalsecondjobs）+总和（totalthirdjobs）+总和（夜间工作）

，（sum（[totalfirstjobs]）* 100）/（sum（[totalfirstjobs]）+ sum（[totalsecondjobs]）+ sum（[totalthirdjobs]）+ [nightwork]）AS percentfirstjobs

FROM site_total

GROUP BY [jobdate]

）tbla



凡投出（[职位日期]为日期） = cast（getdate（）作为日期）

ORDER BY [工作日期]



请注意我'如果可能的话，我们不会处理零除问题。

I think I understand you now. The big issues here are that the data types are ints and nullable, so if there are nulls, the results returned will be null and without using casts (to float) the expression is not commutative.

I'll assume that you don't need to any decimal places (if you do, cast all expressions to floats) and the sums are never null (if they are, use ISNULL function):

SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
,(ISNULL([totalfirstjobs],0)*100)/(ISNULL([totalfirstjobs],0) + ISNULL([totalsecondjobs],0) + ISNULL([totalthirdjobs],0) + [nightwork]) AS percentfirstjobs
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
,(sum([totalfirstjobs])*100)/(sum([totalfirstjobs]) + sum([totalsecondjobs]) + sum([totalthirdjobs]) + [nightwork]) AS percentfirstjobs
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

Please note that I've not dealt with a divide by zero problem if that's a possibility.

这篇关于计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！