How to remove duplicates in one column

Question

The Receipt Qty column is showing duplicated, how to remove duplicates?

select DISTINCT T1."ItemCode" ,T1."ItemName",T2."WhsCode",

(Select IFNULL((Sum("InQty"-"OutQty")),0) from OINM where "DocDate" <[%0] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode") as "Opening Qty",

(select IFNULL ((Sum("Quantity")),0) From PDN1 where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" AND "DocEntry" = T3."DocEntry" AND T3."CANCELED"= 'N') as "Receipt Qty",

((select IFNULL((Sum("OutQty")),0) From OINM where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" and "TransType"='67')) as "Issue Qty",

((Select IFNULL((Sum("InQty"-"OutQty")),0) from OINM where "DocDate" < [%0] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode") +

(select IFNULL ((Sum("Quantity")),0) From PDN1 where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" AND "DocEntry" = T3."DocEntry" AND T3."CANCELED"= 'N') - ((select IFNULL((Sum("OutQty")),0) From OINM where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" and "TransType"='67'))) as "Closing Balance"

FROM OINM T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"

FULL JOIN OPDN T3 ON T0."CardCode" = T3."CardCode" AND T0."DocDate" = T3."DocDate"

FULL JOIN PDN1 T4 ON T0."ItemCode" = T4."ItemCode" AND T0."InQty" = T4."Quantity"

LEFT JOIN OWHS T2 ON T0."Warehouse" = T2."WhsCode"

WHERE T2."WhsCode" = '[%2]'

Group by T1."ItemCode",T1."ItemName",T2."WhsCode",T0."ItemCode",T0."Warehouse", T3."DocEntry", T3."CANCELED"

ORDER BY T1."ItemCode";

NB:

/*select T0."DocDate" AS "DocDate" from OINM T0 where T0."DocDate">=[%0] and T0."DocDate"<=[%1] */

Answer 1

To remove duplicates in the Receipt Qty column, you can modify the subquery to use the DISTINCT keyword inside the SUM function. This will ensure that unique values are summed up. Here's how you can adjust the query:

SELECT DISTINCT
    T1."ItemCode",
    T1."ItemName",
    T2."WhsCode",
    (SELECT IFNULL(SUM("InQty" - "OutQty"), 0)
     FROM OINM
     WHERE "DocDate" < [%0] AND
           "ItemCode" = T0."ItemCode" AND
           "Warehouse" = T2."WhsCode") AS "Opening Qty",
    (SELECT IFNULL(SUM(DISTINCT "Quantity"), 0)
     FROM PDN1
     WHERE "DocDate" >= [%0] AND
           "DocDate" <= [%1] AND
           "ItemCode" = T0."ItemCode" AND
           "Warehouse" = T2."WhsCode" AND
           "DocEntry" = T3."DocEntry" AND
           T3."CANCELED" = 'N') AS "Receipt Qty",
    (SELECT IFNULL(SUM("OutQty"), 0)
     FROM OINM
     WHERE "DocDate" >= [%0] AND
           "DocDate" <= [%1] AND
           "ItemCode" = T0."ItemCode" AND
           "Warehouse" = T2."WhsCode" AND
           "TransType" = '67') AS "Issue Qty",
    ((SELECT IFNULL(SUM("InQty" - "OutQty"), 0)
      FROM OINM
      WHERE "DocDate" < [%0] AND
            "ItemCode" = T0."ItemCode" AND
            "Warehouse" = T2."WhsCode") +
     (SELECT IFNULL(SUM(DISTINCT "Quantity"), 0)
      FROM PDN1
      WHERE "DocDate" >= [%0] AND
            "DocDate" <= [%1] AND
            "ItemCode" = T0."ItemCode" AND
            "Warehouse" = T2."WhsCode" AND
            "DocEntry" = T3."DocEntry" AND
            T3."CANCELED" = 'N') -
     (SELECT IFNULL(SUM("OutQty"), 0)
      FROM OINM
      WHERE "DocDate" >= [%0] AND
            "DocDate" <= [%1] AND
            "ItemCode" = T0."ItemCode" AND
            "Warehouse" = T2."WhsCode" AND
            "TransType" = '67')) AS "Closing Balance"
FROM
    OINM T0
    INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
    FULL JOIN OPDN T3 ON T0."CardCode" = T3."CardCode" AND T0."DocDate" = T3."DocDate"
    FULL JOIN PDN1 T4 ON T0."ItemCode" = T4."ItemCode" AND T0."InQty" = T4."Quantity"
    LEFT JOIN OWHS T2 ON T0."Warehouse" = T2."WhsCode"
WHERE
    T2."WhsCode" = '[%2]'
GROUP BY
    T1."ItemCode", T1."ItemName", T2."WhsCode", T0."ItemCode", T0."Warehouse", T3."DocEntry", T3."CANCELED"
ORDER BY
    T1."ItemCode";

By using the DISTINCT keyword inside the SUM function for the Receipt Qty column subquery, duplicate values will be eliminated, and only unique values will be summed up