From: Peter Brawley Date: October 26 2005 3:26pm Subject: Re: Sum of Sales List-Archive: http://lists.mysql.com/mysql/190893 Message-Id: <435FA02F.9050909@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-435FA02F18C4=======" --=======AVGMAIL-435FA02F18C4======= Content-Type: multipart/alternative; boundary=------------020701080704010106090904 --------------020701080704010106090904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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) 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. > > > --------------020701080704010106090904 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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.

--------------020701080704010106090904-- --=======AVGMAIL-435FA02F18C4======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-435FA02F18C4=======--