May 8, 2012 at 8:52 am
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
May 8, 2012 at 8:59 am
mldardy (5/8/2012)
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 getInvalid 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
1. If you group by LastYearQtyActualContainers and ForecastQtyContainers, you cannot SUM
LastYearQtyActualContainers - ForecastQtyContainers
2. If you want to grou by something you calculate, you need to put the whole formula under GROUP BY, not just the column alias (as you could do for ORDER BY)
May 8, 2012 at 9:20 am
Well I tried something a little different with the difference formula and I get a 'Divide by zero error encountered'. This is on the bolded line below
SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
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, LastYearQtyActualContainers,
ForecastQtyContainers, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers,
ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilname
May 8, 2012 at 9:28 am
mldardy (5/8/2012)
Well I tried something a little different with the difference formula and I get a 'Divide by zero error encountered'. This is on the bolded line belowSELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
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, LastYearQtyActualContainers,
ForecastQtyContainers, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers,
ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilname
you're going to have to handle cases where ForecastQtyContainers =0 you could do somehting like
SUM(case ForecastQtyContainers when 0 then 100 else LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end as [Container Diff% 2010 vs. 2011 Forecast]
but it depends on how you want to represent that figure in your report
MVDBA
May 8, 2012 at 9:32 am
michael vessey (5/8/2012)
mldardy (5/8/2012)
Well I tried something a little different with the difference formula and I get a 'Divide by zero error encountered'. This is on the bolded line belowSELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
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, LastYearQtyActualContainers,
ForecastQtyContainers, Territory, DateHeader, FsMonth, FsYear, ActualPrice, LastYearActualPrice, LastYearQtyActualContainers,
ActualContainers, ForecastQty, ForecastPrice, ForecastQtyContainers order by councilname
you're going to have to handle cases where ForecastQtyContainers =0 you could do somehting like
SUM(case ForecastQtyContainers when 0 then 100 else LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end as [Container Diff% 2010 vs. 2011 Forecast]
but it depends on how you want to represent that figure in your report
1. You do still grouping by the same columns which you're using in aggregate (SUM) function. It does look very wrong!
2. To handle 'Zero' cases you can use: (LastYearQtyActualContainers - ForecastQtyContainers) * 100 / NULLIF(ForecastQtyContainers, 0), this will result as NULL.
May 8, 2012 at 9:37 am
Get an 'incorrect syntax ner ')' error. I'm trying to see what the problem is because this is usually matching '()' but not for sure.
SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
SUM(case ForecastQtyContainers when 0 then 100 else LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end 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 councilname
May 8, 2012 at 9:57 am
mldardy (5/8/2012)
Get an 'incorrect syntax ner ')' error. I'm trying to see what the problem is because this is usually matching '()' but not for sure.SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
SUM(case ForecastQtyContainers when 0 then 100 else LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end 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 councilname
oops - my bad
SUM(case ForecastQtyContainers when 0 then 100 else (LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end )as [Container Diff% 2010 vs. 2011 Forecast],
MVDBA
May 8, 2012 at 4:16 pm
Eugene Elutin (5/8/2012)
1. If you group by LastYearQtyActualContainers and ForecastQtyContainers, you cannot SUMLastYearQtyActualContainers - ForecastQtyContainers
One small correction here Eugene, you can SUM the columns used in GROUP BY, but it wont make any sense, as each GROUPed item will be distinct and a SUM on them is going to be as same as the value of the item itself.
Small test:
select N % 5 a , N * 2 b , N * 3 c
into #t
from tally
where n between 1 and 10
select b,c , sum(b) , sum(c)
from #t
group by b , c
May 9, 2012 at 2:56 am
ColdCoffee (5/8/2012)
Eugene Elutin (5/8/2012)
1. If you group by LastYearQtyActualContainers and ForecastQtyContainers, you cannot SUMLastYearQtyActualContainers - ForecastQtyContainers
One small correction here Eugene, you can SUM the columns used in GROUP BY, but it wont make any sense, as each GROUPed item will be distinct and a SUM on them is going to be as same as the value of the item itself.
Small test:
select N % 5 a , N * 2 b , N * 3 cinto #t
from tally
where n between 1 and 10
select b,c , sum(b) , sum(c)
from #t
group by b , c
That's what I wanted to say, but wasn't clear enough.
You cannot get sum of something (or aggregate this something in any other way) if you are grouping by it.
Sorry for my French 🙂
May 9, 2012 at 6:24 am
michael vessey (5/8/2012)
mldardy (5/8/2012)
Get an 'incorrect syntax ner ')' error. I'm trying to see what the problem is because this is usually matching '()' but not for sure.SELECT CouncilName, LastYearQtyActualContainers as [Containers 2010 Actual], ForecastQtyContainers as [Containers 2011 Forecast],
-SUM(LastYearQtyActualContainers - ForecastQtyContainers) as [Container Diff 2010 vs. 2011],
SUM(case ForecastQtyContainers when 0 then 100 else LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end 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 councilname
oops - my bad
SUM(case ForecastQtyContainers when 0 then 100 else (LastYearQtyActualContainers - ForecastQtyContainers) * 100 / ForecastQtyContainers end )as [Container Diff% 2010 vs. 2011 Forecast],
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.
May 10, 2012 at 6:03 am
mldardy (5/8/2012)
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 getInvalid 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
Introduce an extra level of nesting into your query, using a CTE or a derived table, like this:
SELECT
d.Grp,
d.SUM_Qty,
d.[Rows],
AvgQty = d.SUM_Qty/(d.[Rows]*1.00)
FROM (
SELECT
s.Grp,
SUM_Qty = SUM(s.Qty),
[Rows] = COUNT(*)
FROM (
SELECT Grp = 1, Qty = 3 UNION ALL
SELECT 1, 7 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 9 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 5
) s
GROUP BY Grp
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply