Calculating Summary

[allanfermin]

Hi,

I need help to my Office Supplies inventory

Here are the details:

table name: purchases:

date item qty
4/10/2012 notebook 10
4/11/2012 notebook 10
4/11/2012 bond paper 10
4/12/2012 notebook 10


table name: request
date item qty
4/13/2012 notebook 5
4/14/2012 ballpen 5
4/15/2012 bond paper 5
4/16/2001 notebook 5


My only problem is how can get the total stock
like these:

item qty
notebook 5
ballpen 5
bond paper 5
notebook 10

Thank you in advance!

[eremmel]

Yes I see that you need a computer to do the calculations....

When you have this kind of issues, you need to break it down in sub problems and then build those solutions into the final solution.
So the first is to calculate the totals of purchases and request (this is just typed in and not validated, but hope you have enough understanding of SQL to fix a possible minor typo):

Code: Select all

 SELECT item, sum(qty) AS "PurTotal" FROM purchases GROUP BY item

The totals on request are analogous with total called "ReqTotal".
I assume that you do not deliver requests on items that you never have purchased (that will make things more complicated).
Now we need to combine those two sub-results. There are two possibilities to solve. It think most easy is to save both queries with a logical name like qPurchasesItemTotals and qRequestItemTotals (the 'q' means that it is a named query and not a 't' table or a 'v' view; this helps in understanding the code: queries are part of OOo base document and not part of database).
Now we build a query between those two tables based on the common relation item and add (subtract the totals):

Code: Select all

SELECT p.item AS "Item", (p.PurTotal - r.ReqTotal) AS "CurrentStock" 
FROM qPurchasesItemTotals p LEFT JOIN qRequestItemTotals r ON p.item = r.item 
ORDER BY 1

Note that when you make a typo in a item-name you are lost in this calculation. A slightly improvement is to introduce another table with the item names and give each item an 'id' (unique number) and use that number in both purchases and request tables with mandatory relations, but when this seems to complicated, just forget it

Success,

Erik

[Villeroy]

Example database: http://user.services.openoffice.org/en/ ... 011#p72011

[allanfermin]

This is working

I already do this:
SELECT "item", SUM( "quantity" ) AS "PurTotal" FROM "purchases" GROUP BY "item" ORDER BY "item" ASC


but I can't do this one.



SELECT p.item AS "Item", (p.PurTotal - r.ReqTotal) AS "CurrentStock"
FROM qPurchasesItemTotals p LEFT JOIN qRequestItemTotals r ON p.item = r.item
ORDER BY 1

[allanfermin]

help...

[Villeroy]

help...

Example database: http://user.services.openoffice.org/en/ ... 011#p72011

Code: Select all

SELECT "Articles"."Name",
  SUM( "Input"."Quantity" ) AS "In",
  SUM( "Output"."Quantity" ) AS "Out", 
  SUM( "Input"."Quantity" - "Output"."Quantity" ) AS "Inventory" 
FROM "Input", "Articles", "Output" 
WHERE "Input"."Article_ID" = "Articles"."ID"
  AND "Output"."Article_ID" = "Articles"."ID" 
GROUP BY "Articles"."Name" 
ORDER BY "Articles"."Name" ASC

[allanfermin]

Villeroy,

I'm sorry it's hard to urdertand the code.

[Villeroy]

Villeroy,

I'm sorry it's hard to urdertand the code.

So you can not do it.
Bye

[allanfermin]

I'm trying to do it but I really I can't understand the code.

[allanfermin]

Hi Villeroy,

May I ask some question?

SELECT "Articles"."Name", ====>>> what is the articles?
SUM( "Input"."Quantity" ) AS "In", input is purchases?
SUM( "Output"."Quantity" ) AS "Out", output is request?
SUM( "Input"."Quantity" - "Output"."Quantity" ) AS "Inventory"
FROM "Input", "Articles", "Output"
WHERE "Input"."Article_ID" = "Articles"."ID"
AND "Output"."Article_ID" = "Articles"."ID"
GROUP BY "Articles"."Name"
ORDER BY "Articles"."Name" ASC

[RoryOF]

You need to refer to the sample database linked in Villeroy's reply of 10:34 (UTC+1) earlier today; his example will be based on the fields in that database.

[allanfermin]

Ok thanks I will try.

[allanfermin]

any help?

[thomasjk]

any help?

Please don't keep repeating the same request. Tell us if you have tried what was suggested by Villeroy. If you haven't then we can't help you.

[allanfermin]

I already try but not success. any simple way?

thanks in advanced...

[RoryOF]

No simple way. You have to read up on it and study. We can no more teach you how to program than we can teach you French in two easy lessons.

[Villeroy]

Thanks to this forum's most valued user "eremel" I learned that my above SELECT query is plain wrong. It sums up the wrong row sets. Topic http://user.services.openoffice.org/en/ ... 011#p72011 has a new version of my database attached.