Нужна помощь в доработке запроса

Question

Нужна помощь в доработке данного запроса:

SELECT  
 
       [HospitalID_Ref]
      ,[MedicalDocumentGUID]
      ,[PatientID_Ref]
      ,[REMDID]
      ,[ErrorType]
      ,[MedicalDocumentTypeID_Ref]
     MedicalDocumentTypeID_Ref,

    sum (case when MedicalDocumentTypeID_Ref = 45 and REMDID is not null then 1 else 0 end) as '002', 
    sum (case when MedicalDocumentTypeID_Ref = 46 and REMDID is not null then 1 else 0 end) as '003',
    sum (case when MedicalDocumentTypeID_Ref = 46 and REMDID is not null then 1 else 0 end) + sum (case when MedicalDocumentTypeID_Ref = 45 and REMDID is not null then 1 else 0 end) as 'ИТОГ'

 FROM [REMD].[dbo].[MedicalDocument]

 where MedicalDocumentTypeID_Ref in (45,46) and MedicalDocumentDate between '20230306' and '20230312 23:59:59'

 group by HospitalID_Ref, MedicalDocumentTypeID_Ref, MedicalDocumentGUID, PatientID_Ref, 
REMDID, ErrorType

order by HospitalID_Ref, PatientID_Ref

На данный момента он выводит такие строки:

вопрос состоит в том чтобы в последнем столбце считались суммы строк с одинаковыми столбцами "patientid_ref", при этом remdid is not null, чтобы в итоге была одна строчка, неважно какая из этих двух в конкретном примере, главное чтобы в поле ИТОГ была цифра 2 т е сумма полей 002 и 003 надеюсь я понятно изложил пример итога, который хочу получить ниже:

Answer 1

Ну тут есть два варианта:

1. Сгруппировать только то, что нужно. Отсеяв ненужные столбцы.

SELECT
  [HospitalID_Ref],
  [PatientID_Ref],
  SUM(CASE WHEN [MedicalDocumentTypeID_Ref] = 45 THEN 1 ELSE 0 END) AS [002],
  SUM(CASE WHEN [MedicalDocumentTypeID_Ref] = 46 THEN 1 ELSE 0 END) AS [003],
  COUNT(1) [ИТОГ]
FROM
  [REMD].[dbo].[MedicalDocument]
WHERE
  [MedicalDocumentTypeID_Ref] IN (45, 46)
  AND [REMDID] IS NOT NULL
  AND [MedicalDocumentDate] BETWEEN '20230306' AND '20230312 23:59:59'
GROUP BY
  [HospitalID_Ref],
  [PatientID_Ref]
ORDER BY
  [HospitalID_Ref],
  [PatientID_Ref]

2. Либо использовать оконные функции без группировки.

SELECT
  [HospitalID_Ref],
  [MedicalDocumentGUID],
  [PatientID_Ref],
  [REMDID],
  [ErrorType],
  [MedicalDocumentTypeID_Ref],
  SUM(CASE WHEN [MedicalDocumentTypeID_Ref] = 45 AND [REMDID] IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY [HospitalID_Ref], [PatientID_Ref]) AS [002],
  SUM(CASE WHEN [MedicalDocumentTypeID_Ref] = 46 AND [REMDID] IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY [HospitalID_Ref], [PatientID_Ref]) AS [003],
  SUM(CASE WHEN [REMDID] IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY [HospitalID_Ref], [PatientID_Ref]) AS [ИТОГ]
FROM
  [REMD].[dbo].[MedicalDocument]
WHERE
  [MedicalDocumentTypeID_Ref] IN (45, 46)
  AND [MedicalDocumentDate] BETWEEN '20230306' AND '20230312 23:59:59'
ORDER BY
  [HospitalID_Ref],
  [PatientID_Ref]

БЛОГ НА HUSL

Нужна помощь в доработке запроса

Ответы (1 шт):