find the count? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

find the count?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-08-01 : 14:56:52

Hi all,

I have a table Csutomer with following fields

Customer_no Customer_name, Agent_no

1,X, 1
2,Y,2
3,Z,2
4,A,3

agent table

agent_no,agent_name
1,james
2,Jack
3,Jill
4,Jen

how to loop through this table and find out if the the every agent has equal no of customers

in this case agent_no =2 has two customers but agent-no 4 Jen has no customer then the count should be 1

but for example agent 4 is not at there in agent table then the count should be 2 since agent no 1 and agent_no 3 have only one ticket where as agent_no 2 has two tickets

i need the coumt as 2 (agent no1 and agent_no 3 for assigning any tickets to them)

The reason i doing this is when a new customer 5 is comes in the customer table

i have to check the agents table for any free agent by checking the agent_no field to the agent_no field customer table ..

If all customers have a customer atleast the i hav eto assign the customer to the agent having least customers...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-01 : 19:37:00

Here's a start

DECLARE @customers Table (id int,Customer varchar(1),Agent int)
INSERT INTO @customers
SELECT 1,'X', 1 UNION
SELECT 2,'Y',2 UNION
SELECT 3,'Z',2 UNION
SELECT 4,'A',3

Declare @agents Table (id int,agent varchar(10))
Insert into @agents
select 1,'james' union
select 2,'Jack' union
select 3,'Jill' union
select 4,'Jen'

select a.agent,a.id,count(c.agent) as Customers
from @agents a
left join @customers c on a.id = c.agent
group by a.agent,a.id
order by customers

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 01:14:47

for getting agent with least customers isnt it enough to do like this?


SELECT TOP 1 WITH TIES a.agent_no,a.agent_name
FROM agent a
INNER JOIN (select agent_no,COUNT(*) as custcnt
            FROM Customer 
            GROUP BY agent_no)c
ON c.agent_no = a.agent_no
ORDER BY custcnt ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources