1

I have the following table in SQL Server 2012:

CREATE TABLE [MyTable] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [MyGroup] INT NOT NULL
);

I want to output a table of the form

    | MyGroup | Count
Max |         |
Min |         |

For example, if I have 30 rows in MyTable where MyGroup = 1, 20 rows where MyGroup = 2, and 10 rows where MyGroup = 3, then

    | MyGroup | Count
Max | 1       | 30
Min | 3       | 10

What sort of query would output this information?

7

This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions.

WITH s
     AS (SELECT MyGroup,
                Count(MyGroup)              AS [Count],
                MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
                MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
         FROM   MyTable
         GROUP  BY MyGroup)
SELECT Agg,
       MyGroup,
       V.[Count]
FROM   s
CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [MaxMyGroup] THEN [Count] END),
                     ('Min', CASE WHEN [Count] = [MinMyGroup] THEN [Count] END))
             V(Agg, [Count])
WHERE V.[Count] IS NOT NULL       
1
  • Good one on using cross apply for 'agg'. I was trying to find other ways to populate the 'agg'. Didn't like the additional sorts either. – Travis Jun 29 '13 at 20:02
6
WITH s AS (
  SELECT MyGroup, Count(MyGroup) AS [Count], 
  RANK() OVER (ORDER BY Count(MyGroup)) AS [rasc],
  RANK() OVER (ORDER BY Count(MyGroup) DESC) AS [rdesc]
FROM MyTable
GROUP BY (MyGroup)
)
SELECT 
  CASE 
    WHEN [rasc] = 1 THEN 'Min'
    ELSE 'Max' 
  END AS 'Agg',
  [MyGroup], 
  [Count]
FROM s
WHERE [rasc] = 1 OR [rdesc] = 1

SQLFiddle

Update: Martin's alternative is much efficient.

Here is another similar approach to Martin's solution,

WITH s AS (
SELECT MyGroup, Count(MyGroup) AS [Count], 
  MAX(Count(MyGroup)) OVER () AS [Max],
  MIN(Count(MyGroup)) OVER () AS [Min]
FROM MyTable
GROUP BY MyGroup
)
SELECT 
  CASE 
    WHEN [Count] = [Max] THEN 'Max'
    ELSE 'Min' 
  END AS [Agg],
  [MyGroup], 
  [Count]
FROM s
WHERE [Count] IN ([Max], [Min]);
2
  • 1
    The IN version is simpler than the UNPIVOT with APPLY. One difference would be if there was only one group in MyTable this would return one row rather than two. – Martin Smith Jun 29 '13 at 21:11
  • Thanks @MartinSmith for pointing that out. Anyone interested on how UNPIVOT with APPLY work could find more detail from this link bradsruminations.blogspot.co.uk/2010/02/…. You mentioned that in one of your old post as well. – Travis Jun 30 '13 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.