Getting max count data from a count function

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 2012 Forums

Transact-SQL (2012)

Getting max count data from a count function

Author

Topic

sn06py
Starting Member

3 Posts

Posted - 2015-03-27 : 11:39:31

Hello everyone,

I need help with a problem.

So I have a client table (CT) and a provider table (PT). Both tables are linked with visitID. Client can have many visits with different provider. I need a query to get the result below.

CT.client_name | PT.provider_name | Count_visit (This count visit is look for the provider who each client visit the most, and the number of the visit)

I have no problem to get the count of visit. But how can I let the query shows only the most count with that provider name?

Thanks in advance for your help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 12:54:35

Do you want

1. Client with most visits per provider
or
2. Provider with most visits by any client.

sn06py
Starting Member

3 Posts

Posted - 2015-03-27 : 14:23:58

2. Provider with most visits by any client.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 14:41:57

quote:
Originally posted by sn06py

2. Provider with most visits by any client.

basically:

[code]

select top 1 provider_name, count(client_name)
from provider p
join client c
on p.VisitID = c.VisitID
group by provider desc
order by count(client_name)

sn06py
Starting Member

3 Posts

Posted - 2015-03-27 : 16:49:02

2. Provider with most visits by any client.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-27 : 17:41:34

You really want to have a third table (Visits) which acts as a junction between Client and Provider. This table might only have the client and provider identifiers in it but could also have relevant info on the visit itself (date, time, purpose, etc.). This is a common approach for supporting a Many-to-Many relationship; one Client can have many Providers and one Provider can have many Clients.

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources