/>...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:
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)
GROUP BY r.name;
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):
> 1 Sprocket Supplies CH23 4PY
> The other has the sales by month for each retailer:
> 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?
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 10/25/2005