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?

2 Answers 2

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
    Commented Jun 29, 2013 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. Commented Jun 29, 2013 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
    Commented Jun 30, 2013 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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