Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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       
share|improve this answer
    
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 Gan Jun 29 '13 at 20:02
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]);
share|improve this answer
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 Gan Jun 30 '13 at 2:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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