List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 26 2005 3:36pm
Subject:Re: Sum of Sales
View as plain text  
news <news@stripped> 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

Thread
Sum of SalesDaniel Bowett26 Oct
  • Re: Sum of SalesPeter Brawley26 Oct
    • Re: Sum of SalesDaniel Bowett26 Oct
      • Re: Sum of SalesMichael Stassen26 Oct
        • Re: Sum of SalesDaniel Bowett26 Oct
          • Re: Sum of SalesSGreen26 Oct
          • Re: Sum of SalesPeter Brawley26 Oct
          • Re: Sum of SalesMichael Stassen26 Oct
            • Re: Sum of SalesDaniel Bowett27 Oct
  • Re: Sum of SalesSGreen26 Oct