Calculating Summary
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Calculating Summary
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!
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!
Last edited by allanfermin on Wed Apr 25, 2012 3:41 pm, edited 1 time in total.
OpenOffice3.3
Windows XP
Windows XP
Re: Calculating Summary
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): 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): 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
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
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
Success,
Erik
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: Calculating Summary
Example database: http://user.services.openoffice.org/en/ ... 011#p72011
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
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
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
OpenOffice3.3
Windows XP
Windows XP
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
allanfermin wrote:help...
Villeroy wrote: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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
Villeroy,
I'm sorry it's hard to urdertand the code.
I'm sorry it's hard to urdertand the code.
OpenOffice3.3
Windows XP
Windows XP
Re: Calculating Summary
So you can not do it.allanfermin wrote:Villeroy,
I'm sorry it's hard to urdertand the code.
Bye
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
I'm trying to do it but I really I can't understand the code.
OpenOffice3.3
Windows XP
Windows XP
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
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
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
OpenOffice3.3
Windows XP
Windows XP
Re: Calculating Summary
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
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 wrote:any help?
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
-
- Posts: 9
- Joined: Fri Apr 20, 2012 7:09 am
Re: Calculating Summary
I already try but not success. any simple way?
thanks in advanced...
thanks in advanced...
OpenOffice3.3
Windows XP
Windows XP
Re: Calculating Summary
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Calculating Summary
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice