同时计算多个值但不同类型的值。 [英] to count multiple values at the same time but of different types..

问题描述

例如

 AppNo wc status 
 100 Delhi to HR 
 101 Delhi to wc 
 104 dehradun from wc 
 107 kolkata来自人力资源

我想算一下。来自德里，德拉敦等地的应用程序，有多少申请被发送到人力资源部门，Wc，同样也收到了。



请帮助我。请...

谢谢

解决方案

试试这个：

 < span class =code-keyword> SELECT  [status]，wc  AS  [Place]，COUNT（AppNo） AS  CountOfApps 
  FROM 表1 
  GROUP   BY  [status]，wc 
  ORDER   BY  [status]，wc 

但是如果你想计算不同的应用程序，请使用：

 COUNT（ DISTINCT  AppNo） AS  CountOfDistinctApps 

另一种方法是使用 PIVOT [ ^ ]

  CREATE   TABLE  #tmp（AppNo  INT ，wc  VARCHAR （ 30 ），[status]  VARCHAR （ 30 ））
 
 
 
  INSERT   INTO  #tmp（AppNo，wc，[status]）
  SELECT  < span class =code-digit> 100 ，'  Delhi'，'  to HR' 
  UNION   ALL   SELECT   101 ，'  Delhi'， '  to wc' 
  UNION   ALL   SELECT   104 ，'  dehradun'，' 来自wc' 
  UNION   ALL   SELECT   107 ，'  kolkata'，' 来自HR' 
 
  DECLARE   @cols   NVARCHAR （ 200 ）
 
  SET   @cols  = STUFF（（ SELECT   DISTINCT  ' ]，[' + [status] 
  FROM  #tmp 
  ORDER   BY  ' ]，[' + [status] 
  FOR  XML PATH（' ' ））， 1 ， 2 ，' '）+ ' ]' 
 
 
  DECLARE   @ dt   VARCHAR （ 2000 ）
  DECLARE   @ pt   VARCHAR （MAX）
 
   -   第一个支点 
  SET   @ dt  = N '  SELECT AppNo，wc，[status] 
 FROM #tmp' 
 
  SET   @ pt  = N '  SELECT AppNo，wc，'  +  @cols  + 
 '  FROM（' +  @ dt  + ' ）AS DT 
 PIVOT（COUNT（[status]）FOR [status] IN（' +  @cols  + ' ））AS PT' 
  EXEC （ @ pt ）
 
 
  SET   @cols  = STUFF（ （ SELECT   DISTINCT  ' ]，[' + [wc] 
  FROM  #tmp 
  ORDER   BY  ' ]， [' + [wc] 
  FOR  XML PATH（' '））， 1 ， 2 ，' '）+ ' ]' 
 
 
   -   < span class =code-comment> second pivot  
 
  SET   @ dt  = N '  SELECT AppNo，wc，[status] 
 FROM #tmp' 
 
  SET   @ pt  = N '  SELECT AppNo，[status]，' +  @cols  + 
 '  FROM（' +  @ dt  + ' ）AS DT 
 PIVOT（COUNT（[wc]）FOR [wc] IN（' +  @cols  + ' ））AS PT'  
 
  EXEC （ @ pt ）
 < span class =code-keyword> DROP   TABLE  #tmp 

第一个结果：

 Appno wc从......到... 
 104 dehradun 0 1 0 0 
 100德里0 0 1 0 
 101德里0 0 0 1 
 107 kolkata 1 0 0 0 

第二个结果：

 AppNo wc Dehra。德尔福科尔克。 
 107来自HR 0 0 1 
 104来自wc 1 0 0 
 100到HR 0 1 0 
 101到wc 0 1 0 

从DataBS中获取此数据的位置。

如果此数据来自Dataset，那么您可以使用Dataview。



DataView dv = new DataView（YourDatasetTablename）;

dv.RowFilter = YourFilterValue;

int countval = dv.Count;

试试这个
 选择计数（AppNo）其中 wc = Delhi 

E.g.

AppNo   wc           status        
100     Delhi        to HR
101     Delhi        to wc
104     dehradun     from wc
107     kolkata      from HR

I want to count the no. of applications that came from Delhi,Dehradun and so on other places ,how many applications were sent to HR,to Wc and similarly received also.

Kindly Help me.please...
Thank You

解决方案

Try this:

SELECT [status], wc AS [Place], COUNT(AppNo) AS CountOfApps
FROM Table1
GROUP BY [status], wc
ORDER BY [status], wc

But if you want to count distinct apps, use:

COUNT(DISTINCT AppNo) AS CountOfDistinctApps

Another way is to use PIVOT[^]

CREATE TABLE #tmp (AppNo INT, wc VARCHAR(30), [status] VARCHAR(30))



INSERT INTO #tmp (AppNo, wc, [status])
SELECT 100, 'Delhi', 'to HR'
UNION ALL SELECT 101, 'Delhi', 'to wc'
UNION ALL SELECT 104, 'dehradun', 'from wc'
UNION ALL SELECT 107, 'kolkata', 'from HR'

DECLARE @cols NVARCHAR(200)
 
SET @cols = STUFF((SELECT DISTINCT '],[' + [status]
					FROM #tmp
					ORDER BY '],[' + [status]
			FOR XML PATH('')),1,2,'') + ']'


DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

--first pivot
SET @dt = N'SELECT AppNo, wc, [status]
			FROM #tmp'

SET @pt = N'SELECT AppNo, wc,' + @cols +
		'FROM (' + @dt + ') AS DT
		PIVOT (COUNT([status]) FOR [status] IN (' + @cols + ')) AS PT'
EXEC(@pt)


SET @cols = STUFF((SELECT DISTINCT '],[' + [wc]
					FROM #tmp
					ORDER BY '],[' + [wc]
			FOR XML PATH('')),1,2,'') + ']'


--second pivot

SET @dt = N'SELECT AppNo, wc, [status]
			FROM #tmp'

SET @pt = N'SELECT AppNo, [status],' + @cols +
		'FROM (' + @dt + ') AS DT
		PIVOT (COUNT([wc]) FOR [wc] IN (' + @cols + ')) AS PT'

EXEC(@pt)
DROP TABLE #tmp

first pivot result:

Appno   wc             from...         to...
104	dehradun	0	1	0	0
100	Delhi	0	0	1	0
101	Delhi	0	0	0	1
107	kolkata	1	0	0	0

second pivot result:

AppNo   wc      Dehra.  Delphi  Kolk.
107	from HR	0	0	1
104	from wc	1	0	0
100	to HR	0	1	0
101	to wc	0	1	0

From where you get this data is this in DataSet.
If this data coming from Dataset then you can use Dataview.

DataView dv = new DataView(YourDatasetTablename);
dv.RowFilter = YourFilterValue;
int countval=dv.Count;

try this
select  count (AppNo) where wc= Delhi

这篇关于同时计算多个值但不同类型的值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！