List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 26 2005 8:35pm
Subject:Re: Sum of Sales
View as plain text  
news <news@stripped> wrote on 10/26/2005 04:02:33 PM:

> Michael Stassen wrote:
> > Daniel Bowett wrote:
> > 
> >> Peter Brawley wrote:
> >>
> >>> 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.
> >>>>
> >>>>
> >>>>
> >>>
> >>> 
------------------------------------------------------------------------
> >>>
> >>> 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
> >>>
> >>>
> >>>
> >>> 
------------------------------------------------------------------------
> >>>
> >>>
> >>
> >> Thats workign great, the only problem is the WHERE clause means I 
only 
> >> show rows where there is sales info in the database. Sometimes there 
> >> will be no sales info in there for a particular retailer - would it 
be 
> >> possible to show zero for these?
> > 
> > 
> > Yes.  Change the INNER JOIN to a LEFT JOIN.
> > 
> > Michael
> > 
> > 
> 
> I tried a LEFT JOIN earlier - it still only shows rows where there is
> sales. I think it's because of the WHERE clause.
> 
> 


Did my previous post not work? It should have given you total sales for 
all retailers (regardless of if they had sales in 2005-2005)

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;


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