CODEWITHSUNDEEP
sqlsql-server
Frazer
Frazer

Reputation: 606

SQL group by max count

Ive been looking at several articles on stack but not been exactly specific to what I need

I have a table with application names, teams, service, directorate and username

I want to bring back the application name, team, service, directorate back of the highest used location (team, service, directorate) based on user, ie usercount

SELECT [ApplicationName]
      ,[Team]
      ,[Service]
      ,[Directorate]
      ,count(distinct username) Usercount
FROM 
       [Windows7data].[dbo].[devices_users_apps_detail] a
GROUP BY
       [ApplicationName]
      ,[Team]
      ,[Service]
      ,[Directorate]
ORDER BY
       [ApplicationName], 
       count(distinct username) desc;

I have played with by adding to the above nested subqueries, having statements etc but this has not worked

(Using sub-queries in SQL to find max(count()))

Upvotes: 0

Views: 994

Answers (5)

GarethD
GarethD

Reputation: 69759

You can use the Analytic function RANK to put your team/service/directorate combination in order of number users by Application Name, then just select the top one for each. The key is that ApplicationName appears in the group by clause but not in the Partition by clause of the Rank function.

SELECT  [ApplicationName]
        ,[Team]
        ,[Service]
        ,[Directorate]
        ,UserCount
FROM    (   SELECT  [ApplicationName]
                    ,[Team]
                    ,[Service]
                    ,[Directorate]
                    ,COUNT(DISTINCT username) Usercount,
                    [Rank] = RANK() OVER(PARTITION BY [Team], [Service], [Directorate]   
                                        ORDER BY COUNT(DISTINCT UserName) DESC)
            FROM    [Windows7data].[dbo].[devices_users_apps_detail] a
            GROUP BY [ApplicationName], [Team], [Service], [Directorate]
        ) t
WHERE   t.[Rank] = 1
ORDER BY [ApplicationName], UserCount DESC;

Example on SQL Fiddle

I can't actually work out which way round you want this from the question so I will post both:

SELECT  [ApplicationName]
        ,[Team]
        ,[Service]
        ,[Directorate]
        ,UserCount
FROM    (   SELECT  [ApplicationName]
                    ,[Team]
                    ,[Service]
                    ,[Directorate]
                    ,COUNT(DISTINCT username) Usercount,
                    [Rank] = RANK() OVER(PARTITION BY [ApplicationName] ORDER BY COUNT(DISTINCT UserName) DESC)
            FROM    [Windows7data].[dbo].[devices_users_apps_detail] a
            GROUP BY [ApplicationName], [Team], [Service], [Directorate]
        ) t
WHERE   t.[Rank] = 1
ORDER BY [ApplicationName], UserCount DESC;

Example on SQL Fiddle

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

;With HighestValues
AS
(
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY [Team], [Service], [Directorate] 
                                    ORDER BY Usercount DESC)
 FROM
  (
    SELECT [ApplicationName]
          ,[Team]
          ,[Service]
          ,[Directorate]
          ,count(distinct username) Usercount
    FROM T a
    group by  [ApplicationName],[Team],[Service],[Directorate]
  )Q
)
SELECT APPLICATIONNAME,TEAM,SERVICE,DIRECTORATE,USERCOUNT 
FROM HighestValues
WHERE rn = 1

SQL Fiddle

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

    select *
    from
    (
    SELECT [ApplicationName]
          ,[Team]
          ,[Service]
          ,[Directorate]
          ,count(username) Usercount
    FROM [Windows7data].[dbo].[devices_users_apps_detail] a
    group by  [ApplicationName],[Team],[Service],[Directorate]
    ) b
    inner join (
    select ApplicationName, Max(UserCount) MaxUserCount
    from
    (
    SELECT [ApplicationName]
          ,[Team]
          ,[Service]
          ,[Directorate]
          ,count(distinct username) Usercount
    FROM [Windows7data].[dbo].[devices_users_apps_detail] a
    group by  [ApplicationName],[Team],[Service],[Directorate]
    ) x
)Q on b.ApplicationName = Q.ApplicationName and b.UserCount = q.MaxUserCount

Inner query x gets the lists of Apps, etc with their usercounts. Query Q then get the appname and the max usercount associated with it. This is joined to query b (same as x) which selects the row on b with the maxusercount for each app.

Upvotes: 0

twrowsell
twrowsell

Reputation: 467

Looks OK to me . Does this generate an error? If you want to bring back only the max row then you could try adding a TOP 1 after the SELECT.

Upvotes: 0

Chains
Chains

Reputation: 13157

SELECT TOP(1)
       [ApplicationName]
      ,[Team]
      ,[Service]
      ,[Directorate]
      ,count(distinct username) Usercount
  FROM [Windows7data].[dbo].[devices_users_apps_detail] a
  group by  
       [ApplicationName]
      ,[Team]
      ,[Service]
      ,[Directorate]
  order by 
       Usercount desc;

Upvotes: 0

Related Questions