J'essaie de compter plusieurs lignes individuelles pour chaque colonne. Voici un exemple de ce que j'essaye d'accomplir.
AL A 1 1 1 4 8 9 16 AL B 1 1 5 7 8 9 21
quand je lance ceci, il totalise chaque ligne avec tous les enregistrements et pas seulement les enregistrements de ce chapitre. Des suggestions?
Exemple de sortie
AL A 247 264 247 250 246 235 739 AL B 247 264 247 250 246 235 739 AL G 247 264 247 250 246 235 739 AL D 247 264 247 250 246 235 739 AK A 247 264 247 250 246 235 739 AZ A 247 264 247 250 246 235 739 AZ B 247 264 247 250 246 235 739 AZ G 247 264 247 250 246 235 739
Ce que je veux vraiment, c'est
select (select top 1 name
from chap
where chap.chp_id = CHS.chp_id) as Chap,
(select count(*)
from CHS,
chap
where O_TYPE = 'PRESIDENT'
and chs.chp_id = chap.chp_id) as Presidents,
(select count(*)
from CHS
where O_TYPE = 'VICEPRESIDENT') as VicePresidents,
(select count(*)
from CHS
where OFFICER_TYPE = 'CORRSECRETARY') as CorrespondinSecretaries,
(select count(*)
from CHS
where O_TYPE = 'RECORDINGSECRETARY') as RecordingSecretaries,
(select count(*)
from CHS
where O_TYPE = 'TREASURER') as Treasurers,
(select count(*)
from CHS
where O_TYPE = 'ADVISOR'
and ADV_CODE = 'B') as ChiefAdvisors,
(select count(*)
from CHS
where O_TYPE = 'ADVISOR'
and ADV_CODE <> 'B') as ChiefAdvisors
from CHS
where O_TYPE in ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY', 'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR')
and Term_expire >= DateAdd(Day,DateDiff(Day,0,GetDate()),0)
and Term_Begin <= DateAdd(Day,DateDiff(Day,0,GetDate()),0)
and CHS.CHP_ID in (Select chp_id
from chrs
where active = 'Y')
Group by chs.CHP_ID
3 Réponses :
Pensez à remplacer toutes les sous-requêtes en tant que colonnes calculées par le premier nom étant simplement un JOIN à la table principale. Pour les dénombrements, utilisez l'agrégation conditionnelle en additionnant les conditions. De plus, ajustez le GROUP BY pour inclure nom et non un identifiant unique, ce qui peut être à l'origine de données répétitives. Vérifiez également les plages de dates des termes dans WHERE car elles semblent redondantes.
SELECT chap.name AS Chap,
SUM(O_TYPE = 'PRESIDENT') AS Presidents,
SUM(O_TYPE = 'VICEPRESIDENT') AS VicePresidents,
SUM(OFFICER_TYPE = 'CORRSECRETARY') AS CorrespondingSecretaries,
SUM(O_TYPE = 'RECORDINGSECRETARY') AS RecordingSecretaries,
SUM(O_TYPE = 'TREASURER') AS Treasurers,
SUM(O_TYPE = 'ADVISOR' AND ADV_CODE = 'B') AS ChiefAdvisors,
SUM(O_TYPE = 'ADVISOR' AND ADV_CODE <> 'B') AS OtherAdvisors
FROM chap
INNER JOIN CHS ON chap.chp_id = CHS.chp_id
INNER JOIN chrs ON CHS.CHP_ID = chrs.CHP_ID AND active = 'Y'
WHERE O_TYPE IN ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY',
'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR')
AND Term_expire >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
AND Term_Begin <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
GROUP BY chap.name
L'agrégation conditionnelle doit faire ce que vous voulez. Dans SQL Server, je formulerais ceci comme suit:
SELECT chap.name AS Chap,
SUM(CASE WHEN chs.O_TYPE = 'PRESIDENT' THEN 1 ELSE 0 END) AS Presidents,
SUM(CASE WHEN chs.O_TYPE = 'VICEPRESIDENT' THEN 1 ELSE 0 END) AS VicePresidents,
SUM(CASE WHEN chs.OFFICER_TYPE = 'CORRSECRETARY' THEN 1 ELSE 0 END) AS CorrespondingSecretaries,
SUM(CASE WHEN chs.O_TYPE = 'RECORDINGSECRETARY' THEN 1 ELSE 0 END) AS RecordingSecretaries,
SUM(CASE WHEN chs.O_TYPE = 'TREASURER' THEN 1 ELSE 0 END) AS Treasurers,
SUM(CASE WHEN chs.O_TYPE = 'ADVISOR' AND ADV_CODE = 'B' THEN 1 ELSE 0 END) AS ChiefAdvisors,
SUM(CASE WHEN chs.O_TYPE = 'ADVISOR' AND ADV_CODE <> 'B' THEN 1 ELSE 0 END) AS OtherAdvisors
FROM chap JOIN
CHS
ON chap.chp_id = CHS.chp_id JOIN
chrs
ON CHS.CHP_ID = chrs.CHP_ID AND
WHERE chrs.active = 'Y' AND
chap.O_TYPE IN ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY',
'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR'
) AND
chap.Term_expire >= CONVERT(DATE, GETDATE()) AND
chap.Term_Begin <= CONVERT(DATE, GETDATE())
GROUP BY chap.name;
Notez que cela qualifie également tous les noms de colonnes et simplifie l'arithmétique des dates.
Veuillez essayer ceci:
SELECT chap.Name AS [Chap]
,SUM(IIF(chs.O_TYPE = 'PRESIDENT',1,0)) AS [Presidents]
,SUM(IIF(chs.O_TYPE = 'VICEPRESIDENT',1,0)) AS [VicePresidents]
,SUM(IIF(chs.O_TYPE = 'CORRSECRETARY',1,0)) AS [CorrespondinSecretaries]
,SUM(IIF(chs.O_TYPE = 'RECORDINGSECRETARY',1,0)) AS [RecordingSecretaries]
,SUM(IIF(chs.O_TYPE = 'TREASURER',1,0)) AS [Treasurers]
,SUM(IIF(chs.O_TYPE = 'ADVISOR' AND chs.ADV_CODE = 'B',1,0)) AS [ChiefAdvisorsB]
,SUM(IIF(chs.O_TYPE = 'ADVISOR' AND chs.ADV_CODE <> 'B',1,0)) AS [ChiefAdvisorsNotB]
FROM chs
INNER JOIN chrs ON chrs.CHP_ID = chs.CHP_ID AND chrs.Active = 'Y' /*works as filter*/
INNER JOIN chap ON chap.CHP_ID = chs.CHP_ID
WHERE chs.O_TYPE IN ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY', 'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR')
AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) BETWEEN chs.Term_Begin AND chs.Term_expire
GROUP BY chap.Name
;
Vous avez besoin d'une agrégation conditionnelle où
COUNTest émulé parSUM (CASE WHEN ... THEN 1 ELSE 0)"compter plusieurs lignes individuelles pour chaque colonne" n'est pas clair. Utilisez suffisamment de mots, de phrases et de références à des parties d'exemples pour dire clairement et complètement ce que vous voulez dire. Dites assez que quelqu'un pourrait partir et revenir avec une solution. Dans les questions de code, veuillez donner un exemple minimal reproductible - couper-coller et exécuter un code plus la sortie souhaitée plus des spécifications claires et explication. Minimal signifie ajouter un code de problème minimal à un code de travail minimal. Donc, donnez un code minimal que vous montrez fait ce que vous attendez et un code minimal au premier endroit où vous vous trompez. (Débogage fondamental.)