multiple sums in one select

I have a table that has the following fields: table name: item_hist item_number char(20) year smallint (YYYY) month smallint (1-12) sales decimal(12.2) (unique key = item_number+year+month) I need a single select statement to calculate year-to-date sales and previous year-to-date sales for each item. I come close with the following statement: select lytd.item_num,sum(lytd.sales),sum(ytd.sales) from item_hist lytd, item_hist ytd where lytd.cal_yr="1998" and ytd.cal_yr="1999" and lytd.item_num=ytd.item_num group by lytd.item_num order by lytd.item_num This statement multiplies each 'sum' by the number of occurrences of item_num in the table. Please help! Thanks! -**** Posted from remarQ, Discussions Start Here(tm) ****- http://www.remarq.com/ - Host to the the World's Discussions & Usenet

On Wed, 13 Jan 1999 10:13:38 -0800, bzega@aol.com wrote: >I have a table that has the following fields: > >table name: item_hist > >item_number char(20) >year smallint (YYYY) >month smallint (1-12) >sales decimal(12.2) > >(unique key = item_number+year+month) > >I need a single select statement to calculate year-to-date sales and >previous year-to-date sales for each item. I come close with the following >statement: > >select lytd.item_num,sum(lytd.sales),sum(ytd.sales) > from item_hist lytd, item_hist ytd > where lytd.cal_yr="1998" and > ytd.cal_yr="1999" and > lytd.item_num=ytd.item_num > group by lytd.item_num > order by lytd.item_num > do you want last years 'year to date' sales, or last years total sales? Try this to start with: select i.item_num, (select sum(sales) from item l where year="1998" and i.item_num=l.item_num), (select sum(sales) from item c where year="1999" and i.item_num=c.item_num) from item i group by 1 Good Luck, Douglas Wilson