How to select rows which have max and min of count?

Question

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?

Answer 1

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       

Answer 2

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]);