SELECT f.PAR_FAULT_CODE AS "parFaultCode", (SELECT t.FAULT_NAME FROM FAULT_TYPE t WHERE f.PAR_FAULT_CODE = t.FAULT_CODE) AS "faultName", COALESCE( r.REPORT_DEPT_CODE, 'N/A' ) AS "reportDeptCode", COUNT( r.REPORT_DEPT_CODE ) AS "report", COUNT(*) AS "reportCount" FROM FAULT_TYPE f LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE( r.REPORT_DEPT_CODE, 'N/A' ), f.FAULT_CODE ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME按parfaultcode的report的和加到一起放到reportCount字段怎么修改

可以使用以下SQL语句来实现：

SELECT f.PAR_FAULT_CODE AS "parFaultCode", 
       (SELECT t.FAULT_NAME FROM FAULT_TYPE t WHERE f.PAR_FAULT_CODE = t.FAULT_CODE) AS "faultName", 
       COALESCE(r.REPORT_DEPT_CODE, 'N/A') AS "reportDeptCode", 
       COUNT(r.REPORT_DEPT_CODE) AS "report", 
       SUM(COUNT(r.REPORT_DEPT_CODE)) OVER (PARTITION BY f.PAR_FAULT_CODE) AS "reportCount"
FROM FAULT_TYPE f 
LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE 
GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE(r.REPORT_DEPT_CODE, 'N/A'), f.FAULT_CODE 
ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME

这里使用了窗口函数 `SUM`，对每个 `parFaultCode` 分组的 `report` 字段求和并放到 `reportCount` 字段中。