J'utilise cette database SQL Server. Je définis les remarques suivantes pour les pays
CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'above average' WHEN density_per_sq_km > 250 THEN 'average' WHEN density_per_sq_km > 50 THEN 'below average' ELSE 'Underpopulated' END as remarks
Maintenant, je veux countr combien de pays y a-t-il dans chaque remarque? Comment puis je faire ça? J'utilise la requête suivante mais elle échoue
SELECT COUNT(country) as no_of_counsortinges, CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'above average' WHEN density_per_sq_km > 250 THEN 'average' WHEN density_per_sq_km > 50 THEN 'below average' ELSE 'Underpopulated' END as remarks FROM counsortinges_by_population GROUP BY remarks;
Dans la clause group by vous ne pouvez pas utiliser cet alias de colonne, utilisez plutôt l'expression case
SELECT COUNT(country) AS no_of_counsortinges , CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'above average' WHEN density_per_sq_km > 250 THEN 'average' WHEN density_per_sq_km > 50 THEN 'below average' ELSE 'Underpopulated' END AS remarks FROM counsortinges_by_population GROUP BY CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'above average' WHEN density_per_sq_km > 250 THEN 'average' WHEN density_per_sq_km > 50 THEN 'below average' ELSE 'Underpopulated' END ;
Envelopper la requête avec une colonne calculée dans une sous-requête peut vous aider à utiliser cette colonne:
SELECT remarks, COUNT(country) as no_of_counsortinges FROM ( SELECT CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'above average' WHEN density_per_sq_km > 250 THEN 'average' WHEN density_per_sq_km > 50 THEN 'below average' ELSE 'Underpopulated' END as remarks, country FROM counsortinges_by_population ) DT GROUP BY remarks;
Vous pouvez utiliser CROSS APPLY pour affecter un alias à votre colonne.
SELECT T.Remarks, COUNT(*) AS no_of_counsortinges FROM counsortinges_by_population AS CBP CROSS APPLY ( SELECT CASE WHEN density_per_sq_km > 1000 THEN 'Overpopulated' WHEN density_per_sq_km > 500 THEN 'Above average' WHEN density_per_sq_km > 250 THEN 'Average' WHEN density_per_sq_km > 50 THEN 'Below average' ELSE 'Underpopulated' END ) AS T(Remarks) GROUP BY T.Remarks;
Cela crée une colonne Remarks
basée sur votre densité, qui peut être utilisée par la suite dans GROUP BY
.
Astuce: L'article APPLIQUER et réutilisation des alias de colonnes explique comment utiliser CROSS APPLY
pour créer des alias de colonne réutilisables en détail.