Dan,

>...Total sales for ever would be a simple GROUP BY query with a
>sum on the sales - but I cant see how I am going to get this info.
>Do I need to use nested queries?

You don't need nested queries. It's a crosstab or pivot table query. The trick is to sum into one column per desired year, scope the sums on month-to-date, and group by retailer, eg:

SELECT
r.name,
SUM(IF(LEFT(month_2,4)='2004',sales, '')) AS '2004 Sales',
SUM(IF(LEFT(month_2,4)='2005',sales, '')) AS '2005 Sales'
FROM tbl_retailer AS r
INNER JOIN tbl_sales AS s USING (retailerid)
WHERE SUBSTRING(month_2,6,2)<MONTH(NOW())
GROUP BY r.name;

PB

-----

Daniel Bowett wrote:

I am unsure how to write this query, can someone help?

I have two tables.

One has a list of retailers (tbl_retailer):

retailerid
name
postcode

e.g.

1   Sprocket Supplies   CH23 4PY

The other has the sales by month for each retailer:

retailerid
month_2
sales

e.g.

1   2004-01   100
1   2004-02   400
1   2004-03   300
1   2004-04   200
1   2004-05   300

What I need is a way to output a list of each retailer with two columns at the end being sales this year to date and the equivalent sales for the previous year.

So the columns would be:

name    this_years_sales_to_date    last_years_sales_to_date

Total sales for ever would be a simple GROUP BY query with a sum on the sales - but I cant see how I am going to get this info. Do I need to use nested queries?

Regards,

Dan.