10 messages in com.mysql.lists.mysqlRe: Sum of Sales
FromSent OnAttachments
Daniel Bowett26 Oct 2005 06:46 
Peter Brawley26 Oct 2005 08:26 
SGr...@unimin.com26 Oct 2005 08:35 
Daniel Bowett26 Oct 2005 10:41 
Michael Stassen26 Oct 2005 11:18 
Daniel Bowett26 Oct 2005 13:02 
SGr...@unimin.com26 Oct 2005 13:35 
Peter Brawley26 Oct 2005 13:41 
Michael Stassen26 Oct 2005 13:52 
Daniel Bowett27 Oct 2005 01:20 
Subject:Re: Sum of Sales
From:Daniel Bowett (dan@bowettsolutions.com)
Date:10/26/2005 01:02:11 PM
List:com.mysql.lists.mysql

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.