automatic calculation of data in database.

[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 ]

[Paul Campbell]

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]

Generally you store the details and use 'group by' aggregation queries to create the summaries that you are after.

[prakash chauhan]

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]

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.