SQL : Retourner une colonne contenant le nombre de lignes ayant la même valeur.

Question

J'ai une requête qui renvoie les systèmes et les zones de ma base de données comme suit :

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
              Systems.*

FROM Systems
INNER JOIN Areas ON Areas.ID = Systems.AreaID
WHERE ....

Cela renvoie des données qui ressemblent à ce qui suit :

|  AreaID   |  AreaName   | SystemName  | ...
|     1     |    area1    |     sys1    |
|     1     |    area1    |     sys2    |
|     1     |    area1    |     sys3    |
|     1     |    area1    |     sys4    |
|     2     |    area2    |     sys5    |
|     2     |    area2    |     sys6    |

Je voudrais renvoyer une colonne supplémentaire contenant le nombre de systèmes dans chaque zone retournée, de sorte que j'obtienne quelque chose comme ceci :

|  AreaID   |  AreaName   | SystemName  |  noOfSystems  | ...
|     1     |    area1    |     sys1    |       4       |
|     1     |    area1    |     sys2    |       4       |
|     1     |    area1    |     sys3    |       4       |
|     1     |    area1    |     sys4    |       4       |
|     2     |    area2    |     sys5    |       2       |
|     2     |    area2    |     sys6    |       2       |

C'est-à-dire qu'il y a 4 systèmes dont l'identifiant de zone est 1 et 2 dont l'identifiant de zone est 2.

Comment cela peut-il être fait ? Je suis sûr d'avoir entendu parler d'une fonction intégrée qui fait cela, mais je n'arrive pas à trouver ce que je veux.

Answer 1

Vous voudrez utiliser la fonction d'agrégation COUNT() et ensuite GROUP BY . Cela peut être fait dans une sous-requête corrélée :

SELECT  Areas.AreaID AreaID,
    Areas.areaname AreaName,
    s1.SystemName,
    (select count(systemname) 
     from systems s2
     where s1.areaid = s2.areaid
     group by areaid) noOfSystems
FROM Systems s1
INNER JOIN Areas 
    ON Areas.AreaID = s1.AreaID
WHERE ....

Véase Bricolage SQL avec démo

Ou vous pouvez utiliser une sous-requête que vous joignez pour obtenir le compte total :

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
    s1.SystemName,
    s2.NoOfSystems
FROM Systems s1
INNER JOIN Areas 
    ON Areas.ID = s1.AreaID
INNER JOIN
(
    select COUNT(SystemName) NoOfSystems,
        AreaID
    from systems
    group by AreaID
) s2
    on s1.AreaID= s2.AreaID
WHERE ....

Véase Bricolage SQL avec démo

Cette version utilise une sous-requête pour obtenir le nombre total de personnes, puis la jointure à la base de données de l'entreprise. Systems pour renvoyer des colonnes supplémentaires, si nécessaire.

Ou bien, vous pouvez utiliser les fonctions de fenêtrage si votre SGBDR possède cette option en utilisant Count() over() :

SELECT  Areas.ID AreaID,
    Areas.Name AreaName,
    Systems.SystemName,
    COUNT(SystemName) over(partition by Areas.ID, Areas.AreaName) as NoOfSystems
FROM Systems
INNER JOIN Areas 
    ON Areas.ID = Systems.AreaID
WHERE ....

Véase Bricolage SQL avec démo

Answer 2

Utilisez une sous-requête corrélée comme ceci :

SELECT 
  a.ID AreaID,
  a.Name AreaName,
  s1.*,
  (SELECT COUNT(s2.SystemName)
   FROM Systems s2 
   WHERE a.id = s2.AreasID) AS noofSystem
FROM Systems s1
INNER JOIN Areas a ON a.ID = S1.AreaID
WHERE ....

O:

SELECT  
    Areas.ID AreaID,
    Areas.Name AreaName,
    Systems.SystemName,
    COUNT(Systems.SystemName) AS noOfSystems  
FROM Systems
INNER JOIN Areas ON Areas.ID = Systems.AreaID
WHERE ...
GROUP BY 
    Areas.ID AreaID,
    Areas.Name AreaName,
    Systems.SystemName;

Answer 3

Cela nous donnera le nombre de lignes dans les résultats avec le même AreaID et AreaName que la ligne actuelle. Comme je ne suis pas sûr que AreaName soit unique, je l'inclus dans le calcul de ce nombre :

;WITH cte AS (
    -- Original query
    SELECT Areas.ID AreaID,
        Areas.Name AreaName,
        Systems.SystemName
    FROM Systems
        INNER JOIN Areas ON Areas.ID = Systems.AreaID
    --WHERE...
)
SELECT AreaID,
    AreaName,
    SystemName
    -- Here's where we get the count of rows returned for each unique area
    (SELECT COUNT(*) FROM cte WHERE AreaID = c.AreaID AND AreaName = c.AreaName) AS noOfSystems
FROM cte c

Utilisation d'un expression de table commune de cette façon, nous pourrons utiliser la requête originale sans avoir à la modifier.