如何计算具体价值

问题描述：

我有一个包含一些值的表。表格如下：

I have a table contains some values. the table is given below.

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

我正在使用查询

&gt ;

I'm using the query

>

SELECT DISTINCT TeamName, COUNT(TeamName) AS Count FROM TableA GROUP BY TeamName

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

The above query shows the out put like given below:

TeamName   Count
--------   -----  
 Team1        3    
 Team2        2     
 Team3        2

但我想要展示茶m在gridview中的名称和计数，如下所示，基于workInfo列值。如果`WorkInfo`列包含零，那么我必须得到那个计数并在Count1上显示在Count1上

But I want display the team name and count in gridview like given below based on the workInfo column values. If the `WorkInfo` Column contains zero then I have to get that count and displayed on Count2 else on Count1

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

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

Help me to find a proper solution.Thank you.

答

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 t1
group by TeamName

DEMO [ ^ ]

使用pivot：

Use pivot:

SELECT  TeamName, [1] AS count1, [0] AS count0
FROM
(SELECT TeamName, WorkInfo FROM tableA) AS sourcetable
PIVOT
(
  COUNT(WorkInfo) FOR WorkInfo IN ([1], [0])
)AS somename

了解更多：在SQL查询中使用数据透视的简单方法 [ ^ ]

如何计算具体价值

相关推荐