This is my query so far. But what I need is to return one single value of a column like this.
But in my query so far, this is the returning output.
This my query.
How can I get rid of the double values?
x
SELECT DISTINCT
sol.order_id,
so.name as so_number,
DATE(so.requested_date + INTERVAL '8 HOURS') as delivery_date,
us.name as sales_executive,
rp.partner_type as account_type,
rp.name as account_name,
pt.name as sku,
sol.price_unit as price,
sol.product_uom_qty as ordered_qty,
sol.actual_delivered as delivered_qty,
rb.return_qty,
so.amount_total as gross_sales,
ai.amount_untaxed as vatable_sales,
ai.amount_tax as vat,
ai.residual as net_sales
FROM sale_order so
LEFT JOIN res_partner rp ON rp.id = so.partner_id
LEFT JOIN res_users ru ON ru.id = so.user_id
LEFT JOIN res_partner us ON us.id = ru.partner_id
LEFT JOIN sale_order_line sol ON sol.order_id = so.id
LEFT JOIN product_template pt ON pt.id = sol.product_id
LEFT JOIN account_invoice ai ON ai.origin = so.name
LEFT JOIN return_bottle rb ON rb.sale_id = so.id
WHERE DATE(so.requested_date + INTERVAL '8 HOURS') >= '2020-12-01'
AND DATE(so.requested_date + INTERVAL '8 HOURS') <= '2020-12-30'
AND ai.state != 'cancel'
AND so.name = 'SO11156'
GROUP BY sol.order_id, so.name, rb.return_qty, delivery_date, us.name, rp.partner_type, rp.name, pt.name, sol.price_unit, sol.product_uom_qty, sol.actual_delivered, so.amount_total, ai.amount_untaxed, ai.amount_tax, ai.residual
ORDER BY delivery_date ASC, SOL.ORDER_ID
Advertisement
Answer
You can use analytical function as follows:
Let’s say the column you highlighted is sol.actual_delivered
then use the following expression instead of sol.actual_delivered
case when row_number() over (partition by sol.order_id, so.name order by so.requested_date) = 1
then sol.actual_delivered
end as actual_delivered