List:General Discussion« Previous MessageNext Message »
From:Daniel Bowett Date:October 27 2005 8:20am
Subject: Re: Sum of Sales
View as plain text  
Michael Stassen wrote:
> Daniel Bowett wrote:
> 
>> 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.
> 
> 
> Sorry, my answer was a bit short.  You are right, the WHERE clause is 
> the problem.  Conditions on the right side of a LEFT JOIN need to go in 
> the ON clause, not the WHERE clause, or it defeats the purpose of the 
> LEFT JOIN.  So, your query would be
> 
>   SELECT
>     r.name,
>     SUM(IF(LEFT(s.month_2,4)='2004',s.sales, '')) AS '2004 Sales',
>     SUM(IF(LEFT(s.month_2,4)='2005',s.sales, '')) AS '2005 Sales'
>   FROM tbl_retailer AS r
>   LEFT JOIN tbl_sales AS s
>     ON r.retailerid = s.retailerid
>    AND SUBSTRING(s.month_2,6,2) < MONTH(NOW())
>   GROUP BY r.name;
> 
> Michael
> 
> 

Thanks for your help - that's working really well now.

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