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

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