Gross profit Percentage calculation in SAP B1 query

[Former Member]

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

[kothandaraman_nagarajan · Accepted solution]

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

[Former Member · Accepted solution]

I have a HANA server:-( it gives me error for almost everything... can anyone suggest what would the same query look like in HANA??

Thanks

[kothandaraman_nagarajan · Accepted solution]

I don't have HANA client to check and advice.

[Former Member]

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

[Former Member]

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 "STC_LIVE"."OINV"  T0 INNER JOIN "STC_LIVE"."INV1"  T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."CardCode" = '21155' and  YEAR(T0."DocDate") = '2015' GROUP BY T1."ItemCode", T1."Dscription"

this gives an error too as i am in hana system

[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