List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:October 26 2005 8:53pm
Subject:Re: Sum of Sales
View as plain text  
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

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