從 CTE 獲取聚合數據

我有一個 CTE 語句，它為我返回行項目資訊。這很棒，但我需要對其進行操作，以便僅按符合條件的員工獲取匯總計數。例如，執行此查詢時返回的數據

Employee saleid   SaleAmt  SaleDate
Emp A    abc123   1100  2017-01-03
Emp A    def456   1200  2017-01-03
Emp B    ghi789   1500  2017-01-09

現在我想要返回的只是

Employee  Count
Emp A 2
Emp B 1

應如何修改此查詢以僅返回聚合數據？**注意我正在使用RTRIM()andLTRIM()函式，因為有時使用者會在員工姓名之前或之後輸入額外的空格（很快就會進行清理:)–)

WITH DollaAmt AS 
(
   Select 
   cm.empName employee
   ,jb.saleID saleID
   ,CAST(COALESCE(jp.saleamt,0) As DOUBLE PRECISION) DollaAmt
   ,CAST(jp.saleDate As Date) saleDate
   FROM salePieces jp
   Inner Join sale jb
   on jp.saleID = jb.saleID
   Inner join employee cm
   on cm.esID = jb.esID
   WHERE jp.saleDate BETWEEN '2017-01-01' AND '2017-03-31'
   AND cm.empStatus IN ('A', 'E', 'L')
   OR RTRIM(LTRIM(cm.empName)) = 'Trainee'
),
employees As
(
   select 
   cm.empName As Employee
   ,cm.empStatus As employeeStatus
   FROM employee cm
   ORDER BY Employee ASC
)
Select
DISTINCT(RTRIM(LTRIM(Employee))) As "employeeName"
,ia.saleID
,"TotalDollaAmt"
,ia.saleDate
FROM employees cm
JOIN (
       Select
       RTRIM(LTRIM(employee)) As employee
       ,SUM(DollaAmt) As "TotalDollaAmt"
       ,saleDate
       ,saleID
       FROM DollaAmt 
       GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'

聽起來您只需要將外部查詢更改為 GROUP BY 員工姓名：

...
SELECT
(RTRIM(LTRIM(Employee))) As "employeeName"
,COUNT(1) AS "Count"
FROM employees cm
JOIN (
       Select
       RTRIM(LTRIM(employee)) As employee
       ,SUM(DollaAmt) As "TotalDollaAmt"
       ,saleDate
       ,saleID
       FROM DollaAmt 
       GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'
GROUP BY (RTRIM(LTRIM(Employee)))

引用自：https://dba.stackexchange.com/questions/172899