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

Attachment: [text/html]
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
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