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

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