如何使用sql Query获取基于计数的百分比？ [英] How to get percentage based on count using sql Query?

问题描述

下面给出的是数据库表（TableA）。

Given below is the database table(TableA).

SlNo  TeamName  WorkInfo
----  -------   -------
1     Team1        0
2     Team1        0
3     Team1        1
4     Team2        1
5     Team2        1
6     Team2        1
7     Team3        0
8     Team3        1

我有两个文本框用于选择开始和结束日期。我希望在这些日期之间显示详细信息。



以下查询根据开始日期和结束日期提供详细信息和计数。

I have two text boxes for selecting start and end date. I want to display details in between that dates.

Given below query is giving the details and count based on the start date and end date.

SELECT TeamName,Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) AS Count1 ,
          Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) AS Count0 FROM tableA
   WHERE (SubmitDate BETWEEN @start AND @end) GROUP BY TeamName

以上查询显示如下所示的输出：

The above query shows the out put like given below:

TeamName   Count1   Count0
 --------   -----    ------
 Team1        1        2
 Team2        3        0
 Team3        1        1

我想根据Count1和Count0中的最大计数显示百分比。帮我修改上面的解决方案。 `期望的输出样本如下：`



这里的`Count1` 3是greates值。基于此我必须找到Count1值的百分比。并且在`Count0`中2是最大值。基于此我必须找到Count0值的百分比。

I want to display the percentage based on the `greatest count` in `Count1` and `Count0` . Help me to modify the above solution. `The expecting output sample is given below:`

Here in `Count1` 3 is the greates value.Based on that I have to find the percentage of Count1 values. And in `Count0` 2 is the greatest value.Based on that I have to find the percentage of Count0 values.

TeamName   Count1   Count0  Percentage1  Percentage0
--------   -----    ------  -----------  -----------
Team1        1        2        33.33%         100%
Team2        3        0        100%           0%
Team3        1        1        33.33%         50%

帮我找到合适的解决方案。谢谢。

Help me to find a proper solution. Thank You.

推荐答案

这是你已经发布过两次的同一个问题：

如何使用SQL查询获取计数和百分比？ [ ^ ]

如何计算具体值 [ ^ ]



你两次都得到了很好的答案。因此，这一次（以及将来）开始研究您所拥有的解决方案，并思考它们如何工作并使用这些信息来尝试自己解决这个问题。每次你需要一个小小的调整时，我们不能也不会为你做你的工作：我们不是为了这个。从长远来看，你更快地了解自己在做什么，而不是每次都要问我们！

This is the same question as you have posted twice already:
How to get count and percentage using SQL Query?[^]
How get count for specific value[^]

And you have had good answers both times. So this time (and in future) start looking at the solutions you have and thinking about how they work and use that information to try and solve this yourself. We cannot and will not do your work for you each time you need a minor tweak: we are not here for that. And it's quicker for you in the long run to understand what you are doing than have to ask us each and every time!

这篇关于如何使用sql Query获取基于计数的百分比？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！