List: General Discussion « Previous MessageNext Message » From: Daniel Bowett Date: October 27 2005 8:20am Subject: Re: Sum of Sales View as plain text
```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.

```