I am using a Select statement where I get the difference between two columns (ForecastQtyContainers and LastYearQtyActualContainers). I'm able to get that fine but I want take the Difference (using the COUNT function) from those two and divide it by LastYearQtyActualContainers.
The problem is the Difference column is not an actual database column but I'm using it as an 'As'. How can I get the Difference column to work I get
Invalid column name 'Container Diff 2010 vs. 2011'. error.
Here is my select statement:
SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
Count([Container Diff 2010 vs. 2011])* 100/LastYearQtyActualContainers,
ActualContainers, Product, ActualQty,
Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice,LastYearActualQty,
ActualContainers, ForecastPrice, ForecastQtyContainers
from TmpLYData group by LastYearActualQty, ActualQty, Product, CouncilName, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers,
ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilname
Thanks for this. This works. Another question is how do I get it to show the percent sign or decimal places. It looks like it is rounding up or down. Is there a way to do that using a formula like this.
bootzilla
Member
137 Points
597 Posts
Need help with SQL statement using COUNT function
May 08, 2012 02:46 PM|LINK
I am using a Select statement where I get the difference between two columns (ForecastQtyContainers and LastYearQtyActualContainers). I'm able to get that fine but I want take the Difference (using the COUNT function) from those two and divide it by LastYearQtyActualContainers. The problem is the Difference column is not an actual database column but I'm using it as an 'As'. How can I get the Difference column to work I get
Invalid column name 'Container Diff 2010 vs. 2011'. error.
Here is my select statement:
SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast], SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011], Count([Container Diff 2010 vs. 2011])* 100/LastYearQtyActualContainers, ActualContainers, Product, ActualQty, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice,LastYearActualQty, ActualContainers, ForecastPrice, ForecastQtyContainers from TmpLYData group by LastYearActualQty, ActualQty, Product, CouncilName, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers, ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilnameimobsuz
Participant
1278 Points
195 Posts
Re: Need help with SQL statement using COUNT function
May 08, 2012 03:02 PM|LINK
I do not quite understand the calculation, but try this:
Hope this helps.
bootzilla
Member
137 Points
597 Posts
Re: Need help with SQL statement using COUNT function
May 08, 2012 03:31 PM|LINK
I get a divide by zero error encountered on that line.
SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast], SUM(LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers as [Container Diff% 2010 vs. 2011 Forecast] ,ActualContainers, -SUM(LastYearQtyActualContainers - ActualContainers) as [Container Diff vs. 2010 Actual], -SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff vs. 2011 Forecast], LastYearActualPrice, Product, ActualQty, Territory, DateHeader, FsMonth, FsYear, ActualPrice,LastYearActualQty, ActualContainers, ForecastPrice, ForecastQtyContainers from TmpLYData group by LastYearActualQty, ActualQty, Product, CouncilName, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers, ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilnameimobsuz
Participant
1278 Points
195 Posts
Re: Need help with SQL statement using COUNT function
May 08, 2012 06:11 PM|LINK
Try:
(case when ForecastQtyContainers = 0 then 0 else SUM(LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end) as [Container Diff% 2010 vs. 2011 Forecast],Hope this helps.
bootzilla
Member
137 Points
597 Posts
Re: Need help with SQL statement using COUNT function
May 09, 2012 02:39 PM|LINK
Thanks for this. This works. Another question is how do I get it to show the percent sign or decimal places. It looks like it is rounding up or down. Is there a way to do that using a formula like this.
imobsuz
Participant
1278 Points
195 Posts
Re: Need help with SQL statement using COUNT function
May 09, 2012 03:11 PM|LINK
Try:
(case when ForecastQtyContainers = 0 then 0.0 else SUM(LastYearQtyActualContainers - ForecastQtyContainers) * 100.0 / ForecastQtyContainers end) as [Container Diff% 2010 vs. 2011 Forecast],ps: please mark as answer if this solves your issue
Hope this helps.
yrb.yogi
Star
14460 Points
2402 Posts
Re: Need help with SQL statement using COUNT function
May 10, 2012 05:59 AM|LINK
try below query
.Net All About