File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes automatic calculation of data in  database. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "automatic calculation of data in  database." Watch "automatic calculation of data in  database." New topic
Author

automatic calculation of data in database.

prakash chauhan
Ranch Hand

Joined: Jun 22, 2007
Posts: 81
Hi All,
I have a table that has a the following field

DATE,ITEM_SOLD,QTY_SOLD ,<QTY_SOLD_MTD>


This table will have count of how many pieces of the items were sold
on that particular DATE, i want to have an additional column that will
hold how many items have been sold so far How can it be achieved , what are my options? I am using MySql as underlying database and using Java as programming language.

Thanks for the help.
[ November 07, 2007: Message edited by: prakash chauhan ]
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by prakash chauhan:
Hi All,
I have a table that has a the following field
DATE,ITEM_SOLD,QTY_SOLD ,<QTY_SOLD_MTD>


This table will have count of how many pieces of the items were sold
on that particular DATE, i want to have an additional column that will
hold how many items have been sold so far How can it be achieved , what are my options? I am using MySql as underlying database and using Java as programming language.

Thanks for the help.

[ November 07, 2007: Message edited by: prakash chauhan ]


if you restructure your table into three tables... one table with your date dimension, item dimension, and a third for your fact with date_key and item_key you can aggregate your data with a simple query.

date_dim_table
date_key
item_date

item_dim_table
item_key
item_id

item_fact
date_key
item_key
qty_sold

select sum(qty_sold)
from item_fact
join date_dim_table using (date_key)
join item_dim_table using (item_key)
where item_id = myItem
and month(item_date) = 11 /* if you want the 11th month to compare to */

This also allows you to get total sales for a single item regardless of date.

select sum(qty_sold)
from item_fact
join item_dim_table using (item_key)
where item_id = myItem

Total sales for a day, month, year... in this case... total for the month


select sum(qty_sold)
from item_fact
join date_dim_table using (date_key)\
where month(item_date) = 11 /* if you want the 11th month to compare to */

[ November 08, 2007: Message edited by: Paul Campbell ]
[ November 08, 2007: Message edited by: Paul Campbell ]
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
Generally you store the details and use 'group by' aggregation queries to create the summaries that you are after.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
prakash chauhan
Ranch Hand

Joined: Jun 22, 2007
Posts: 81
Thanks for the information .
I ll try to implement it and see how it works.
actullly i am new to it so still learning..
Thanks again .
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
you can also easily do what steve indicates via a single table... as a rule, you should always store your data at an atomic level... storing a calculation that is dependent on you aggregating your data is self-defeating because it will be worthless as soon as your business rules change. If you store at your most granular level, you will not paint your self into a proverbial corner.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: automatic calculation of data in database.