Have a filtered value and total grouped by date-CodePudding

I am currently working on PostgreSQL having 2 data tables, Operator and Order.

Each Operator does an order and this will create a new order with its name, date and Operator ID.

I want to display, for each day where there have been at least one order, the total number of orders done by all the operators and the number of order of a specific operator that I put the id.

I'm actually doing:

SELECT TO_DATE(Order.order_end_date, 'DD/MM/YYYY') as orderDate,
COUNT(Order.order_index) AS totalOrders
FROM Order
LEFT JOIN Operator ON Order.op_id = Operator.operator_id
AND TO_DATE(order_end_date, 'DD/MM/YYYY') BETWEEN '01/10/2021' AND '22/10/2021'
group by orderDate
ORDER BY orderDate ASC

This displays for each day the total number of orders done by all the operators :

2021-10-15 | 3
2021-10-16 | 2
2021-10-18 | 4

I would like to be able to put on a third column the number of orders done by the operator with operator_id = 6

2021-10-15 | 3 | 0
2021-10-16 | 2 | 2
2021-10-18 | 4 | 1

CodePudding user response：

You can use the FILTER clause:

count(*) filter (where Order.op_id = 6)

This will only count the rows where the expression yields true.

Alternatively, you can use a CASE:

count(case when op_id = 6 then 1 end)

This relies on the fact that COUNT only counts non-NULL values, and the default for CASE is NULL when no WHEN clause matched.

CodePudding user response：

SELECT TO_DATE(Order.order_end_date, 'DD/MM/YYYY') as orderDate,
COUNT(Order.order_index) AS totalOrders,
SUM(CASE WHEN Order.op_id = 6 THEN 1 ELSE 0 END) AS totalOrdersOperator6
FROM Order
WHERE TO_DATE(Order.order_end_date, 'DD/MM/YYYY') BETWEEN '01/10/2021' AND '22/10/2021'
group by orderDate
ORDER BY orderDate ASC