Reputation: 1111
I have data in table 1 that shows the service code for customers along with their service date. The table #2 shows the service code and the order number associated with that service code and when that order took place i.e., report_date. As shown in the desired_result, I want to see the count of distinct orders for each service code that took place after the service_date.
Upvotes: 0
Views: 466
Reputation: 4503
Here is the query that returns srvc_cust_code
and distinct count of orders per srvc_cust_code
where the report_date
is later than service_date
:
SELECT srvc_cust_code, count(DISTINCT orders) FROM table1
JOIN table2 USING (srvc_cust_code)
WHERE report_date > service_date
GROUP BY srvc_cust_code;
If you need more info, please take a look at the PostgreSQL Documentation:
7.2. Table Expressions (infomation on JOIN
, USING
and GROUP BY
)
4.2.7. Aggregate Expressions (information on function count()
and DISTINCT
)
Upvotes: 3