news <[EMAIL PROTECTED]> wrote on 10/26/2005 09:46:49 AM:
> 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.
>
>
Try this. It's a variation on the pivot table technique. We are
column-izing on a the year value derived from the string pattern of your
month_2 column:
SELECT r.name
, sum(if(sbm.month_2 between '2004' and '2004-99',sbm.sales, 0))
as sales_2004
, sum(if(sbm.month_2 between '2005' and '2005-99',sbm.sales, 0))
as sales_2005
FROM retailers r
LEFT JOIN sales_by_month sbm
on sbm.retailerid = r.retailerid
GROUP BY r.name;
That should give you the yearly sales figures for every retailier in your
system for cy2004 and cy2005 (cy = calendar year)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
