SQL returning 1 single column value on a multiple row

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?

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

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

Advertisement

Answer