Skip to Content
avatar image
Former Member

Gross profit Percentage calculation in SAP B1 query

Hi...

i am writing a query with the sales for particular customer and in a particular year for a particular product.

SELECT Distinct(T1."ItemCode"), T1."Dscription",SUM(T1."Quantity"),SUM(T1."LineTotal"),SUM(T1."GrssProfit" )

FROM "STC_LIVE"."OINV" T0 INNER JOIN "STC_LIVE"."INV1" T1

ON T0."DocEntry" = T1."DocEntry"

WHERE T0."CardCode" = '12345' and YEAR(T0."DocDate") = '2015'

GROUP BY T1."ItemCode", T1."Dscription"

Its giving me right output.

But now i want to add one more column in the query to calculate gross profit

which would be SUM(T1."GrssProfit" )/SUM(T1."LineTotal") * 100 .

how can i write it in query and how can i make sure division by zero overflow does not happen?

Regards,

Anjana

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 06, 2016 at 10:40 PM

    Hi

    Even if remove where condition in SQL, iam not getting such error message.

    SELECT Distinct(T1.ItemCode), T1.Dscription,SUM(T1.Quantity),SUM(T1.LineTotal),SUM(T1.GrssProfit ),Case WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0 ELSE

    SUM(IsNUll(T1.GrssProfit ,0))/Sum(isnull(T1.LineTotal,0)) * 100 END 'Gross Profit %' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry GROUP BY T1.ItemCode, T1.Dscription

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 06, 2016 at 01:13 PM

    SELECT Distinct(T1."ItemCode"),T1."Dscription",SUM(T1."Quantity"),SUM(T1."LineTotal"),

    SUM(T1."GrssProfit" ),(SUM(T1."GrssProfit" )/SUM(T1."LineTotal"))*100 AS "GP" FROM "STC_LIVE"."OINV" T0 INNER JOIN "STC_LIVE"."INV1" T1

    ON T0."DocEntry" = T1."DocEntry"

    WHERE T0."CardCode" = '12345' and YEAR(T0."DocDate") = '2015'

    GROUP BY T1."ItemCode", T1."Dscription"

    this addition give me an error

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Anjana,

      If the error is related to division by zero, then you have to insert CASE conditions like CASE WHEN SUM(T1."LineTotal") = 0 then 0 else SUM(T1."GrssProfit" )/SUM(T1."LineTotal") * 100 END.

      Try if this works.

      ALAIN