Postgresql – Get Aggregate Data From CTE

performancepostgresqlpostgresql-9.4query-performancet-sql

I have a CTE statement that returns line item information for me. This is great, but I need to manipulate this in order to only get aggregate counts by Employee where the criteria is met. For example data when this query is executed this is returned

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

Now all that I want returned is

Employee  Count
Emp A 2
Emp B 1

How should this query be modified in order to only return aggregate data?
**Note I am using the RTRIM() and the LTRIM() functions as sometimes a user will input extra whitespace before or after an employee name (cleanup is going to take place soon :)–)

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'

Best Answer

It sounds like you just need to change your outer query to also GROUP BY the employee name:

...
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)))
Related Question