Daniel Bowett wrote:
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.
Sorry, my answer was a bit short. You are right, the WHERE clause is the
problem. Conditions on the right side of a LEFT JOIN need to go in the ON
clause, not the WHERE clause, or it defeats the purpose of the LEFT JOIN. So,
your query would be
SELECT
r.name,
SUM(IF(LEFT(s.month_2,4)='2004',s.sales, '')) AS '2004 Sales',
SUM(IF(LEFT(s.month_2,4)='2005',s.sales, '')) AS '2005 Sales'
FROM tbl_retailer AS r
LEFT JOIN tbl_sales AS s
ON r.retailerid = s.retailerid
AND SUBSTRING(s.month_2,6,2) < MONTH(NOW())
GROUP BY r.name;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]