Division column only returns one row

Question

I'm writing a SQL query to check Gross Profit Percentage for a group of sales items in a database.

SELECT T0.[itmsgrpcod], 
       T0.sell, 
       T0.cost, 
       ( T0.sell / T0.cost ) AS "GP" 
FROM   (SELECT T0.[itmsgrpcod], 
               Sum(T1.[price])      AS "Sell", 
               Sum(T1.[stockprice]) AS "Cost" 
        FROM   inv1 T1 
               LEFT OUTER JOIN oitm T0 
                            ON T1.[itemcode] = T0.[itemcode] 
        GROUP  BY T0.[itmsgrpcod]) T0 

I'm having an odd problem in that when I have the SELECT statement as:

SELECT T0.[ItmsGrpCod], T0.Sell, T0.Cost

It returns 96 rows - the correct amount, with Sell and Cost data filled.

When I add the column:

    (T0.Sell / T0.Cost) as "GP"

It returns only the first row of the query, with GP calculated properly.

Answer 1

It turns out that SAP Business One's Query Generator does not report Division by Zero. When I tried the query in SQL Server Management Studio, it provided a proper Division by Zero error, and I fixed the problem.

Complete query, for those interested:

SELECT T0.ItmsGrpCod,
       SUM(T1.Price) As "Sell",
       ISNULL(SUM(T1.StockPrice), 0) As "Cost",
       CASE WHEN SUM(T1.StockPrice) = 0 THEN 100 
            ELSE (SUM(T1.Price) - SUM(T1.StockPrice)) / SUM(T1.Price) * 100
       END As "GP"
FROM INV1 T1 LEFT OUTER JOIN OITM T0 ON T1.ItemCode = T0.ItemCode
GROUP BY T0.ItmsGrpCod
ORDER BY T0.ItmsGrpCod