t-sql GROUP BY avec COUNT, puis inclure MAX du COUNT

Mécaniquement, il ya quelques façons de le faire. Vous pouvez utiliser des tables temporaires/variable de table. Une autre façon est avec des requêtes imbriquées et/ou un CTE @Aaron_Bertrand a montré. Une troisième façon est d'utiliser la FENÊTRE des FONCTIONS telles que...

SELECT    CarName,
          COUNT(*) as theCount,
          MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxPerGroup
FROM      dbo.tbl_Cars
GROUP BY CarName

Un non grata (lire depricated) quatrième moyen est d'utiliser le CALCUL de mot-clé en tant que tel...

SELECT   CarID, CarName, Count(*)
FROM     dbo.tbl_Cars
GROUP BY CarID, CarName 
COMPUTE MAX(Count(*))   

La COMPUTE mot-clé génère totaux qui apparaissent comme complémentaires colonnes de synthèse à la fin de l'ensemble de résultats (voir ce). Dans la requête ci-dessus, vous en fait voir de deux ensembles d'enregistrements.

Plus rapide

Maintenant, la prochaine question est, quel est le "meilleur/le plus rapide/la plus facile." Je pense tout de suite à un indexed view. @Aaron doucement m'a rappelé, les vues indexées ont toutes sortes de restrictions. Ci-dessus, la stratégie, cependant, vous permet de créer une vue indexée sur le SELECT...from..GROUPE. Puis en sélectionnant à partir de la vue indexée appliquer le mode FENÊTRÉ clause de FONCTION.

Sans en savoir plus, cependant, à propos de votre design, il va être difficile pour quiconque de vous dire ce qui est le meilleur. Vous obtiendrez d'éclairage rapide des requêtes à partir d'une vue indexée. Cette performance a un prix, cependant. Le prix est frais de maintenance. Si la table sous-jacente est la cible d'une grande quantité d'insérer/mettre à jour/supprimer des opérations de la maintenance de la vue indexée ralentisse la performance dans d'autres domaines.

Si vous partagez un peu plus sur votre cas d'utilisation, de données et de modèles d'accès de gens seront en mesure de partager plus de perspicacité.

MICRO TEST DE PERFORMANCE

J'ai donc généré un peu de données de script et regarda le générateur de profils sql numéros de la CTE de la performance vs fenêtré fonctions. C'est un micro-test, alors essayez certains des nombres réels votre système sous de charge réel.

La génération de données:

Create table Cars ( CarID int identity (1,1) primary key, 
                    CarName varchar(20), 
                    value int)
GO
insert into Cars (CarName, value)
values  ('Buick', 100),
        ('Ford', 10),
        ('Buick', 300),     
        ('Buick', 100),
        ('Pontiac', 300),       
        ('Bmw', 100),
        ('Mecedes', 300),       
        ('Chevy', 300),     
        ('Buick', 100),
        ('Ford', 200);
GO 1000

Ce script génère 10 000 lignes. J'ai ensuite couru chacun des quatre requêtes suivantes à plusieurs reprises :

--just group by
select  CarName,COUNT(*) countThis
FROM    Cars
GROUP BY CarName        

--group by with compute (BAD BAD DEVELOPER!)
select  CarName,COUNT(*) countThis
FROM    Cars
GROUP BY CarName        
COMPUTE  MAX(Count(*));

-- windowed aggregates...
SELECT  CarName,
        COUNT(*) as theCount,
        MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxInAnyGroup
FROM Cars
GROUP BY CarName        

--CTE version
;WITH x AS (
  SELECT   CarName,
           COUNT(*) AS Total
  FROM     Cars
  GROUP BY CarName
)
SELECT x.CarName, x.Total, x2.[Max Total]
FROM x CROSS JOIN (
  SELECT [Max Total] = MAX(Total) FROM x
) AS x2;

Après l'exécution de la ci-dessus requêtes, j'ai créé une vue indexée sur le "groupe" de la requête ci-dessus. Ensuite, j'ai couru une requête sur la vue indexée qui a effectué un MAX(Count(*)) OVER(PARTITION BY 'foo'.

LA MOYENNE DES RÉSULTATS

Query                      CPU       Reads     Duration   
--------------------------------------------------------
Group By                   15        31        7 ms  
Group & Compute            15        31        7 ms
Windowed Functions         14        56        8 ms 
Common Table Exp.          16        62       15 ms
Windowed on Indexed View    0        24        0 ms

Évidemment, c'est un micro-benchmark et seulement légèrement instructif, afin de les prendre pour ce qu'elle vaut.

SQL Server 2008 R2 et les versions plus récentes, vous pouvez utiliser :

GROUP BY CarID, CarName WITH ROLLUP