in SQL,how to Count() ids of column containing different values ?

Question

0.00/5 (No votes)

See more:

I have a table called FollowupMaster, which contains entries of all the employees .
It has a column named 'FollowupMode' which contains string values like 'Call','Housecall','Email','SMS'..
And now i need to count number of 'Housecall','call' made by Each employee

CSS

1   1   2014-02-12 00:00:00.000 Call    Switch Off  Mobile  2014-03-13 00:00:00.000 19  NULL    1   2014-03-28 07:28:55.453 NULL
2   56  2014-04-05 00:00:00.000 Call    WILL TELL US MONDAY 2014-04-07 00:00:00.000 28  2   1   2014-04-05 01:49:10.730 NULL
3   57  2014-04-04 00:00:00.000 Call    WILL COME WITH PARENTS FOR VISIT    2014-04-06 00:00:00.000 30  2   32  2014-04-05 04:26:37.610 NULL
4   827 2007-01-06 00:00:00.000 House Call  next week   2007-01-13 00:00:00.000 34  4   37  2014-04-26 07:11:16.500 NULL
5   827 2007-01-13 00:00:00.000 E-Mail  not     2007-01-15 00:00:00.000 34  4   37  2014-04-26 07:11:46.497 NULL
6   827 2007-01-13 00:00:00.000 Call    sda 2007-01-20 00:00:00.000 33  4   37  2014-04-26 07:13:57.397 NULL
7   83  2013-06-06 00:00:00.000 Call    Fiist   2013-06-06 00:00:00.000 34  4   37  2014-04-29 07:19:11.673 NULL
8   817 2014-05-26 00:00:00.000 House Call  Next Time   2014-05-28 00:00:00.000 29  0   28  2014-05-26 14:14:53.703 NULL
9   817 2014-05-28 00:00:00.000 Call    Over    2014-05-30 00:00:00.000 30  0   28  2014-05-26 14:15:37.670 NULL
10  818 2014-05-26 00:00:00.000 House Call  try 2014-05-28 00:00:00.000 31  0   28  2014-05-26 14:26:18.020 NULL
11  818 2014-05-28 00:00:00.000 SMS 2nd try 2014-05-30 00:00:00.000 31  0   28  2014-05-26 14:26:40.480 NULL
12  816 2014-05-26 00:00:00.000 Call    try 2014-05-28 00:00:00.000 28  2   28  2014-05-26 14:35:50.533 NULL
13  816 2014-05-28 00:00:00.000 House Call  t   2014-05-30 00:00:00.000 28  2   28  2014-05-26 14:49:32.687 NULL
14  949 2014-05-26 00:00:00.000 House Call  11  2014-05-28 00:00:00.000 30  2   30  2014-05-26 20:07:31.183 NULL
15  950 2014-05-26 00:00:00.000 SMS 222 2014-05-30 00:00:00.000 30  2   30  2014-05-26 20:14:55.677 Closed
16  818 2014-05-28 00:00:00.000 SMS aas 2014-05-28 00:00:00.000 30  2   28  2014-05-27 10:17:29.387 NULL
17  818 2014-05-28 00:00:00.000 House Call  test    2014-05-30 00:00:00.000 30  2   28  2014-05-27 10:24:20.437 NULL
18  818 2014-05-28 00:00:00.000 House Call  test    2014-05-30 00:00:00.000 30  2   28  2014-05-27 10:33:08.023 NULL
19  816 2014-05-26 00:00:00.000 E-Mail  2nd tym 2014-05-30 00:00:00.000 28  2   28  2014-05-27 10:43:44.510 NULL
20  950 2014-05-26 00:00:00.000 SMS Enq1    2014-05-28 00:00:00.000 30  2   30  2014-05-27 12:32:03.283 Closed
21  950 2014-05-28 00:00:00.000 E-Mail  Closing 2014-05-28 00:00:00.000 30  2   30  2014-05-27 12:32:46.873 Closed
22  950 2014-05-28 00:00:00.000 E-Mail  Closing 2014-05-28 00:00:00.000 30  2   30  2014-05-27 12:33:20.460 Closed

Posted 27-May-14 21:35pm

rahulDer

Add a Solution

Comments

Kornfeld Eliyahu Peter 28-May-14 3:38am

Google for SQL GROUP BY...

King Fisher 28-May-14 3:38am

how can we go through this Table? Do you?

show your query ,any error in your Query?

rahulDer 28-May-14 4:02am

here is my queries ..i want this result in single query:
select count(followupid) from erp.followupmaster where followupby=28 and followupmode='call'
select count(followupid) from erp.followupmaster where followupby=28 and followupmode='Housecall'

King Fisher 28-May-14 5:20am

Try this
select count(followupid) from erp.followupmaster where followupby=28 and followupmode in ('call','Housecall') group by followupby

rahulDer 28-May-14 3:43am

here is my queries ..i want this result in single query:
select count(followupid) from erp.followupmaster where followupby=28 and followupmode='call'
select count(followupid) from erp.followupmaster where followupby=28 and followupmode='Housecall'

3 solutions

Solution 1

You can use SQL GROUP BY[^], for example

SQL

select empIdColumn, Count(*)
from yourTable 
where FollowupMode = 'House Call'
group by FollowupMode,empIdColumn

Posted 27-May-14 21:42pm

DamithSL

Comments

rahulDer 28-May-14 4:04am

thanq for dis..but what if I want followupmode='Call' fo same employee at same time ?

Solution 2

Group by clause on the employee id and folloupMode would give you the result or even you can use case statement in where clause.

Regards,
Praveen Nelge

Posted 27-May-14 22:36pm

praveen_07

Updated 27-May-14 23:32pm

v3

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)

rahulDer · Accepted Answer · 2014-05-28T00:07:00

Here i have used temparory Table for concating all required data.

SQL

CREATE TABLE ##GlobalTempTable1(
	
	Empid int,
	Enq int,
	Call1 int,
	HouseCall int,
	Email int,
	Sms int 
	
)
declare @FranchiseId int
declare @Empid int
declare @Enq int
declare @Call int
declare @HouseCall int
declare @Email int
declare @Sms int
declare @ID int

set @ID = 2
select @Empid = max(followupby) from ERP.FollowUpMaster where franchiseid=2 
while (@ID <= @Empid)
begin
	select @HouseCall=count(followupid) from ERP.FollowUpMaster 
	where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
	--and franchiseid=2 
	and followupby=@ID and followupmode='House call'

select @Email=count(followupid) from ERP.FollowUpMaster 
	where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
	--and franchiseid=2 
	and followupby=@ID and followupmode='E-mail' 
	

select @Call=count(followupmode) from ERP.FollowUpMaster 
	where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
	--and franchiseid=2 
	and followupby=@ID and followupmode = 'call'


select @Sms=count(followupid) from ERP.FollowUpMaster 
	where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
	--and franchiseid=2 
	and followupby=@ID and followupmode='SMS'
	if(@HouseCall != 0 or @Call != 0 or @Email != 0 or @Sms != 0)
	begin
	INSERT into ##GlobalTempTable1(
		--FranchiseId,
		Empid,
		--Enq,
		Call1 ,
		HouseCall ,
		Email ,
		Sms  
	)
		values
	(
		--@franchiseId,
		@ID,
		@Call,
		@HouseCall,
		@Email,
		@Sms
		
	)
	END
	SET @franchiseId = ''
	SET @Enq = ''
	
	SET @Sms = ''
	SET @Call = ''
	SET	@HouseCall = ''
	SET @Email = ''
	
	SET @ID = @ID + 1
	END
	select * from ##GlobalTempTable1
	
	drop table ##GlobalTempTable1