Re: Can someone please help me with a SELECT?

Belakimem wrote: > > Can someone help me with a SELECT? > > I have a SELECT > > SELECT YEAR, CUSTOMER, PRODUCT, PERIOD, REGION, SALES from my_table > order by YEAR, CUSTOMER, PRODUCT, PERIOD, REGION> > which works fine. > > Now here is the hard bit, I want to add a "derived" column : ytd_sales > > ytd_sales corresponds to the sum of the sales, for every combination of YEAR, > CUSTOMER, PRODUCT. > > In other words I want to find the ytd_sales for the current YEAR, CUSTOMER, > PRODUCT regardless of the period and region and include it in the returned row. > > It has to be pure SQL. > > As an example, suppose I had only 4 rows in the table , the output would look > like : > > YEAR CUSTOMER PRODUCT PERIOD REGION SALES YTD_SALES > 97 1 1 11 1 2 10 > 97 1 1 11 2 3 10 > 97 1 1 12 1 4 10 > 97 1 1 12 2 1 10 > > I hope I've explained it well! > > Any suggestions gratefully received! Try this: SELECT year, customer, product, SUM(sales) AS ytd_sales FROM my_table GROUP BY year, customer, product ORDER BY year, customer, product Another observation: the two-digit year may need some thought soon. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/