to count multiple values at the same time but of different types..

Question

1.00/5 (1 vote)

See more:

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

Posted 17-Jul-13 21:41pm

Shivangi Rawat

Updated 17-Jul-13 21:45pm

Maciej Los

v3

Add a Solution

Comments

Thanks7872 18-Jul-13 3:48am

As per i understand your question,something is going from delhi to HR with app no 100.
something from delhi to wc with app no 101,and so on?

Shivangi Rawat 18-Jul-13 4:04am

yes,now i have to count the number of all such applications denoted by AppNo that are going to different places(say Delhi,Dehradun....)and to whom(HR,Wc) and received from (HR,Dehradun...) so that i can prepare a report based on that.
so how should i do it?

Maciej Los 18-Jul-13 4:11am

See my answer ;)

3 solutions

Solution 2

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;

Posted 17-Jul-13 21:50pm

syed shanu

Solution 3

SQL

try this
select  count (AppNo) where wc= Delhi

Posted 17-Jul-13 22:25pm

Dholakiya Ankit

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-07-17T21:49:00

Try this:

SQL

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:

SQL

COUNT(DISTINCT AppNo) AS CountOfDistinctApps

Another way is to use PIVOT[^]

SQL

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