SQL group by max count

Question

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()))

yeah what is the primary key on the `devices_users_apps` _detail table? — Frank Conry, Jan 03 '14 at 16:03
It looks like you are looking for the highest user count for combination of (team, service, directorate) for every application. Is that right? — entomo, Jan 03 '14 at 16:07

GarethD · Accepted Answer · 2014-01-03T16:30:47.400

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

+1 for the answer and explaining why my answer wasnt right :) — M.Ali, Jan 03 '14 at 16:19
Thanks this seems to have worked, couldnt Use Top 1 as i wanted to group by the 4 columns. I do have some multi rows apps on row having blank locations in them and want to remove those without removing single row blank apps, this may not be possible. — Frazer, Jan 03 '14 at 16:37

score 0 · Answer 2 · answered Jan 03 '14 at 16:05

0

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;

answered Jan 03 '14 at 16:05

Chains

12,541
8
45
62

I don't think you can `order by` an aliased field at the same level it was aliased. I think you need to `ORDER BY COUNT(DISTINCT username)` instead. – Eli Gassert Jan 03 '14 at 16:07
1

@EliGassert -- I think once you get to the ORDER BY clause (which is processed *after* the SELECT clause) you can order by any alias in the SELECT section...at least in TSQL – Chains Jan 03 '14 at 16:08

M.Ali · Answer 3 · 2014-01-03T16:30:45.707

0

;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

edited Jan 03 '14 at 16:30

answered Jan 03 '14 at 16:05

M.Ali

67,945
13
101
127

1

dang just beat me! Good goin! – Frank Conry Jan 03 '14 at 16:06
1

Inner query Q will return usercount for all App/Tean/Svc/Directorates and the outer query will group the data in the same way. The outer query will not change the resultset of the inner query surely? – simon at rcl Jan 03 '14 at 16:09
@simonatrcl Just to prove your point I've added this to query to [an SQL Fiddle](http://sqlfiddle.com/#!3/c9ac0/5) showing the results of just the inner query and the query as a whole are identical (and also the same results as the query posted in the question) – GarethD Jan 03 '14 at 16:16
@GarethD Cheers pal, appreciated – M.Ali Jan 03 '14 at 16:17
1

No worries, be aware though that using `ROW_NUMBER()` will only return one row even if two have the same usercount. I deliberately set the fiddle up so THE combination of `Team 2, Service 1, Directorate 1`, had 2 rows for application 1 and 2 rows for application 2, to demonstrate this. Using Rank will return both rows, row_number will only return one of them – GarethD Jan 03 '14 at 16:43

score 0 · Answer 4 · answered Jan 03 '14 at 16:06

0

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.

answered Jan 03 '14 at 16:06

twrowsell

467
3
8

score 0 · Answer 5 · answered Jan 03 '14 at 16:19

    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.

SQL group by max count

5 Answers5