Michael Stassen wrote:
> 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
>
>
Thanks for your help - that's working really well now.
