aspose file tools
The moose likes JDBC and Relational Databases 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


Win a copy of Groovy Fundamentals video training course this week in the Groovy forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 862
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.