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

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