One to many query: return most common value

Question

I am working on a one to many query for an assets database.  Here is some sample data




Server Name        Application        Primary_User
Server1                 SQL                   DBA
Server1                 Citrix                 IT
Server1                 Oracle                DBA
Server2                 Sharepoint         Web
Server3                 SQL                   DBA
Server3                 Sharepoint         Web
Server3                 Norton               Security
Server3                 IDS                   Security

The desired output is one row per server with the server name, count of applications, and the Primary User that appears the most (not just the first, last, min, or max).

It would look like this

Server Name    Applications  Primary_User
Server1            3                  DBA
Server2            1                  Web
Server3            4                  Security


Is there a sub query that can accomplish this?  Thanks in advance!

Answer 1

Hi Andy,
First of all this is a question for T-SQL forum...

Anyways ,
Your query for this :

SELECT b.[Server Name],c.[Primary_User],b.count2 AS [Applications] FROM

(SELECT a.[Server Name],a.[Primary_User],ROW_NUMBER() OVER (PARTITION BY a.[Server Name] ORDER BY a.count1 DESC)as rownumber

FROM

(SELECT [Server Name],[Primary_User],COUNT([Primary_User]) as count1

FROM Table1 Group BY [Server Name],[Primary_User])a )c

 

INNER JOIN

(SELECT [Server Name],COUNT([Application]) as count2

FROM Table1

Group BY [Server Name])b ON c.[Server Name]=b.[Server Name]

WHERE c.rownumber=1

I think this can be written may be a little smaller…

Please Vote & "Mark As Answer" if this post is helpful to you.

Cheers
Bikash Dash
MCDBA/MCITP

Answer 2

Thank you for your response Bikash.  Your query is returning a syntax error for me.  It may be because this SQL query must be executed in Excel.

Answer 3

Hi Andy,
Give me the error message if it is not solved yet, i can do it for you.
I have prepared the query and tested it.

Please Vote & "Mark As Answer" if this post is helpful to you.

Cheers
Bikash Dash
MCDBA/MCITP

Answer 4

So I finally worked through this issue.  The only way I could get it to work was by first creating a view.

VIEW:

SELECT Assets.ASSET_NAME as Name, Count( DISTINCT Applications.APP_NAME)AS App_Count, [User Groups].USER_GROUP AS USER_GROUP

into #tempdd

FROM Assets

INNER JOIN [Link Application to Asset] ON Assets.SEQUENCE = [Link Application to Asset].SEQ_ASSET

INNER JOIN Applications ON [Link Application to Asset].SEQ_APP = Applications.SEQUENCE

INNER JOIN [User Groups] ON Applications.SEQ_USERS = [User Groups].SEQUENCE

GROUP BY Assets.ASSET_NAME , [User Groups].USER_GROUP

ORDER BY Assets.ASSET_NAME , [User Groups].USER_GROUP

Query:

SELECT b.Name, b.APP_Count, b.User_Group

FROM

(SELECT Name, MAX(APP_Count) as MAX_count from #tempdd group by Name)

as x

INNER JOIN #tempdd b on b.name = x.name and b.app_count = x.MAX_count

ORDER BY Name