如何显示基于SQL Server 2012中其他字段的公式的自定义字段？ [英] How to display a customized field which is based on formulas of other fields in SQL server 2012?

问题描述

大家好

这篇文章可能看起来与我以前的一个问题重复。但我想新发布。以下是查询： -

Hi All
This post may seem repetitive to one of my previous questions.But I thought to post freshly. Following is the query:-

SELECT
    JOB_NAME,
    SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
    COUNT(JOB_NAME) As OverallCount
FROM
    COMP_HIS_TBL
GROUP BY
    JOB_NAME

我需要在结果中添加一个名为FailPercent的附加字段:( Fail_Count / Overall_Count）* 100.



< b>我尝试了什么：



我试过以下阙但它的抛出错误:(

I need to add an additional field called "FailPercent" based on the formula :(Fail_Count/Overall_Count)*100 in the result.

What I have tried:

I tried the following queries but its throwing error:(

SELECT
    JOB_NAME,
    SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
    COUNT(JOB_NAME) As OverallCount,
	(Fail_Count/OverallCount)*100 as Fail_Percent
FROM
    COMP_HIS_TBL
GROUP BY
    JOB_NAME)

2）

2)

Select JOB_NAME,T1.Fail_Percent  from
(SELECT
    JOB_NAME,
    SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
    COUNT(JOB_NAME) As OverallCount,
	(Fail_Count/OverallCount)*100 as Fail_Percent
FROM
    COMP_HIS_TBL
GROUP BY
    JOB_NAME)as T1

3）

3)

Select JOB_NAME,(Count(Fail_Count)/Count(OverallCount))*100 as Fail_percent,STATUS from COMP_HIS_TBL 
where Fail_Count,OverallCount in (SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME) 

请提供一个解决方案:(

谢谢

Please provide a solution to this:(
Thank you

推荐答案

您需要阅读SQL的Windows函数 - 如何使用Microsoft SQL Server 2012的窗口函数，第1部分|来自SQL Server Pro的SQL Server 2012内容 [ ^ ]



尝试此查询以查看它是否符合您的要求：

You need to read up on SQL's Windows Functions - How to Use Microsoft SQL Server 2012's Window Functions, Part 1 | SQL Server 2012 content from SQL Server Pro[^]

Try this query to see if it meets your requirements:

SELECT
    JOB_NAME, [Status],
    COUNT(*) OVER (PARTITION BY [Status]) As Fail_Count,
    COUNT(JOB_NAME) OVER() As OverallCount,
    100.0 * COUNT(*) OVER (PARTITION BY [Status]) / COUNT(JOB_NAME) OVER() as FailPercent

FROM
    COMP_HIS_TBL

如果您只是想看看故障那么将它用作子查询

If you just want to look at the failures then use it as a sub-query

SELECT * FROM
(
	SELECT
		JOB_NAME, [Status],
		COUNT(*) OVER (PARTITION BY [Status]) As Fail_Count,
		COUNT(JOB_NAME) OVER() As OverallCount,
		100.0 * COUNT(*) OVER (PARTITION BY [Status]) / COUNT(JOB_NAME) OVER() as FailPercent

	FROM
		COMP_HIS_TBL
) src
WHERE [Status] = 'Failure'

这篇关于如何显示基于SQL Server 2012中其他字段的公式的自定义字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！