[Solved] Sumif type function to use only positive numbers

[amitmahajan74]

Hi,

I have created a DB for daily production of articles against orders. I am stuck in a query. I get the following fields in query for the daily production report.

1. Total order
2. Production Till Date
3. Production Today
4. Total Production
5. Balance Production

Now there are times when the Total Production exceeds the Total Order and in this case the Balance Production shows as negative value. I use the query in Calc in Pivot table and in the row total at the end, it adds all the negative values too, thus giving a variation in Balance Production. The Balance production is being calculated by (sum(TotalOrder) - sum(TotalProduction)).

I would want to use a function like sumif, the above calculation with the option to give the result if it is positive only, negative result it should give the result as zero value.

Thanks,

Amit

[Villeroy]

download/file.php?id=24572 (simple inventory)
Simple SUMIF in SQL:

Code: Select all

SELECT SUM("Values") AS  FROM "Something" WHERE "Values">0

[amitmahajan74]

Thanks Villeroy, I tried using it but now it's giving the results for values where sum is >0 but eliminating the results where it is less than zero. I am attaching the complete query here, please advise ..

Code: Select all

SELECT "Customer"."Customer", "OrdersLine"."Order No ID", "Order"."Season", "Category"."Category", "Order"."Facility", "Order"."Status", "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size", ( COALESCE ( "OR"."OrderQty", 0.0 ) * "Order"."Rate" ) AS "OrderValue", COALESCE ( "OR"."OrderQty", 0.0 ) AS "Order", COALESCE ( "PB"."ProdQtyBefore", 0.0 ) AS "PrTilDat", COALESCE ( "PTdy"."ProdQtyToday", 0.0 ) AS "PrToday", COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdTotal", COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdBal" FROM "OrdersLine" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "OrdQty" ) AS "OrderQty" FROM "OrdersLine" GROUP BY "Order No ID", "Size ID" ) AS "OR" ON "OrdersLine"."Order No ID" = "OR"."Order No ID" AND "OrdersLine"."Size ID" = "OR"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyBefore" FROM "OrdersLine" WHERE "ProdDate" < :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PB" ON "OrdersLine"."Order No ID" = "PB"."Order No ID" AND "OrdersLine"."Size ID" = "PB"."Size ID" LEFT OUTER JOIN ( SELECT "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment", SUM( "OrdersLine"."ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine", "Department" WHERE "OrdersLine"."ProdDate" = :RpDt AND "OrdersLine"."Dept ID" = "Department"."ID" GROUP BY "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment" ) AS "PTdy" ON "OrdersLine"."Order No ID" = "PTdy"."Order No ID" AND "OrdersLine"."Size ID" = "PTdy"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyTotal" FROM "OrdersLine" WHERE "ProdDate" <= :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PTotal" ON "OrdersLine"."Order No ID" = "PTotal"."Order No ID" AND "OrdersLine"."Size ID" = "PTotal"."Size ID" INNER JOIN "Order" ON "OrdersLine"."Order No ID" = "Order"."Order ID" INNER JOIN "Product" ON "Order"."Product ID" = "Product"."ID Number" INNER JOIN "Colour" ON "Order"."Colour ID" = "Colour"."ID Number" INNER JOIN "Size" ON "OrdersLine"."Size ID" = "Size"."ID Number" INNER JOIN "Customer" ON "Product"."Cust ID" = "Customer"."ID" INNER JOIN "Category" ON "Product"."Category ID" = "Category"."ID Number" INNER JOIN "Plan" ON "Order"."Order ID" = "Plan"."OrderNoID" 

WHERE COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) > 0 

and MONTH( "Plan"."Plan Month" ) = :mth AND YEAR( "Plan"."Plan Month" ) = :yr GROUP BY "OrdersLine"."Order No ID", "Order"."Season", "Order"."Facility", "Order"."Status", "Order"."Order No", "Category"."Category", "Product"."Product Name", "Colour"."Colour", "Size"."Size", "OR"."OrderQty", "PB"."ProdQtyBefore", "PTdy"."ProdQtyToday", "PTotal"."ProdQtyTotal", "Customer"."Customer", "Order"."Rate" ORDER BY "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size"

[amitmahajan74]

Got it, used casewhen in the query to use query value >0 else 0. Working now.

Thanks