[Solved] Sumif type function to use only positive numbers

Creating tables and queries

[Solved] Sumif type function to use only positive numbers

Postby amitmahajan74 » Thu Mar 31, 2016 10:14 am

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
Last edited by amitmahajan74 on Fri Apr 01, 2016 10:38 am, edited 1 time in total.
Open Office 4.1.2
Win 10 Proff
amitmahajan74
 
Posts: 44
Joined: Mon Aug 27, 2012 12:23 pm

Re: Using Sumif type function to use only positive numbers

Postby Villeroy » Thu Mar 31, 2016 3:20 pm

https://forum.openoffice.org/en/forum/d ... p?id=24572 (simple inventory)
Simple SUMIF in SQL:
Code: Select all   Expand viewCollapse view
SELECT SUM("Values") AS  FROM "Something" WHERE "Values">0
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Sumif type function to use only positive numbers

Postby amitmahajan74 » Fri Apr 01, 2016 7:43 am

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   Expand viewCollapse view
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"
Open Office 4.1.2
Win 10 Proff
amitmahajan74
 
Posts: 44
Joined: Mon Aug 27, 2012 12:23 pm

Re: Using Sumif type function to use only positive numbers

Postby amitmahajan74 » Fri Apr 01, 2016 10:38 am

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

Thanks
Open Office 4.1.2
Win 10 Proff
amitmahajan74
 
Posts: 44
Joined: Mon Aug 27, 2012 12:23 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest