trbbhm
asked on
Restructure SQL Query To Use Column Alias in GROUP BY
I hope someone can help me restructure this query so that I can use "divi" in my GROUP BY statement. Right now I'm getting an "invalid column name" for "divi" because it's an alias, but I don't know how to get around it with this particular statement.
select
case
when ASC_BR.dbo.MthEndInvtDivVend.Division between 10 and 19 then '10'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 20 and 29 then '20'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 30 and 39 then '30'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 40 and 49 then '40'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 50 and 59 then '50'
else
ASC_BR.dbo.MthEndInvtDivVend.Division
end as divi,
ASC_BR.dbo.VENDOR.Name,
ASC_BR.dbo.MthEndInvtDivVend.VendorNum,
round(abs(sum(ASC_BR.dbo.mthendSalesDivVend.SalesRtl)/AVG(nullif(invcost,0))),2) as turn,
round(AVG(NULLIF(ASC_BR.dbo.MthEndInvtDivVend.InvCost,0)),0) as InvCost,
SUM(ASC_BR.dbo.mthendSalesDivVend.SalesRtl) as SalesRtl,
SUM(ASC_BR.dbo.mthendSalesDivVend.SalesCost) as SalesCost,
avg(nullif(ASC_BR.dbo.MthEndInvtDivVend.OHQty,0)) as QtyOH,
sum(ASC_BR.dbo.mthendSalesDivVend.SalesQty) as SlsQty,
sum(salesrtl)-sum(salescost) as GM$,
round((sum(salesrtl)-sum(salescost))/nullif(sum(salesrtl),0)*100,2)
from ASC_BR.dbo.MTHENDINVTDIVVEND
left outer join ASC_BR.dbo.VENDOR on ASC_BR.dbo.VENDOR.VendorNum=ASC_BR.dbo.MTHENDINVTDIVVEND.Vendornum
left outer join ASC_BR.dbo.MthEndSalesDivVend on
ASC_BR.dbo.mthendSalesDivVend.Division=ASC_BR.dbo.MTHENDINVTDIVVEND.Division and
ASC_BR.dbo.mthendSalesDivVend.VendorNum=ASC_BR.dbo.MTHENDINVTDIVVEND.VendorNum and
ASC_BR.dbo.mthendSalesDivVend.YearMth=ASC_BR.dbo.MTHENDINVTDIVVEND.YearMth
where left(ASC_BR.dbo.MTHENDINVTDIVVEND.Division,1) in ('1','2','3','4','5','6')
and asc_br.dbo.MTHENDINVTDIVVEND.YearMth>=convert(char(4),year(DATEADD(month,-12,GETDATE())))+right('00'+convert(varchar(2),month(DATEADD(month,-12,GETDATE()))),2)
and asc_br.dbo.MTHENDINVTDIVVEND.YearMth<=convert(char(4),year(DATEADD(month,0,GETDATE())))+right('00'+convert(varchar(2),month(DATEADD(month,-1,GETDATE()))),2)
group by divi,ASC_BR.dbo.MTHENDINVTDIVVEND.Vendornum,ASC_BR.dbo.VENDOR.Name
order by Name,divi
ASKER
The problem I'm getting is with the GROUP BY statement. The query that you replied with still gave me an "invalid column name 'divi'" in the GROUP BY statement. So I tried to pull it outside of the secondary select statement as follows:
I now get the following error message from the inside SELECT statement:
Column 'ASC_BR.dbo.MTHENDINVTDIVV END.Divisi on' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select X.* FROM (
SELECT
case
when ASC_BR.dbo.MthEndInvtDivVend.Division between 10 and 19 then '10'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 20 and 29 then '20'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 30 and 39 then '30'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 40 and 49 then '40'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 50 and 59 then '50'
else
ASC_BR.dbo.MthEndInvtDivVend.Division
end as divi,
ASC_BR.dbo.VENDOR.Name as vname,
ASC_BR.dbo.MthEndInvtDivVend.VendorNum as vnum,
round(abs(sum(ASC_BR.dbo.mthendSalesDivVend.SalesRtl)/AVG(nullif(invcost,0))),2) as turn,
round(AVG(NULLIF(ASC_BR.dbo.MthEndInvtDivVend.InvCost,0)),0) as InvCost,
SUM(ASC_BR.dbo.mthendSalesDivVend.SalesRtl) as SalesRtl,
SUM(ASC_BR.dbo.mthendSalesDivVend.SalesCost) as SalesCost,
avg(nullif(ASC_BR.dbo.MthEndInvtDivVend.OHQty,0)) as QtyOH,
sum(ASC_BR.dbo.mthendSalesDivVend.SalesQty) as SlsQty,
sum(salesrtl)-sum(salescost) as GM$,
round((sum(salesrtl)-sum(salescost))/nullif(sum(salesrtl),0)*100,2) as GMPct
from ASC_BR.dbo.MTHENDINVTDIVVEND
left outer join ASC_BR.dbo.VENDOR on ASC_BR.dbo.VENDOR.VendorNum=ASC_BR.dbo.MTHENDINVTDIVVEND.Vendornum
left outer join ASC_BR.dbo.MthEndSalesDivVend on
ASC_BR.dbo.mthendSalesDivVend.Division=ASC_BR.dbo.MTHENDINVTDIVVEND.Division and
ASC_BR.dbo.mthendSalesDivVend.VendorNum=ASC_BR.dbo.MTHENDINVTDIVVEND.VendorNum and
ASC_BR.dbo.mthendSalesDivVend.YearMth=ASC_BR.dbo.MTHENDINVTDIVVEND.YearMth
where left(ASC_BR.dbo.MTHENDINVTDIVVEND.Division,1) in ('1','2','3','4','5','6')
and asc_br.dbo.MTHENDINVTDIVVEND.YearMth>=convert(char(4),year(DATEADD(month,-12,GETDATE())))+right('00'+convert(varchar(2),month(DATEADD(month,-12,GETDATE()))),2)
and asc_br.dbo.MTHENDINVTDIVVEND.YearMth<=convert(char(4),year(DATEADD(month,0,GETDATE())))+right('00'+convert(varchar(2),month(DATEADD(month,-1,GETDATE()))),2)
) X
group by divi,vnum,vname
order by vName,divi
I now get the following error message from the inside SELECT statement:
Column 'ASC_BR.dbo.MTHENDINVTDIVV
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So sorry, I misread the question. Because your aggregate functions are in the sub-select, your GROUP BY has to stay there.
ASKER
That is so far over my head, but thank you!!!
Scott, just read your comment. That's nice!
Thanks!
What's really super sweet is that the "alias" from one ca can be used in a subsequent ca. So you can have cascading "aliases"!
The example below is not practical, of course, it just shows what's possible:
SELECT
ca1.table_origin,
ca2.table_code,
o.*
FROM sys.objects o
CROSS APPLY (
SELECT CASE WHEN o.type = 'S' THEN 'System' ELSE 'User' END AS table_origin
) AS ca1
CROSS APPLY (
SELECT CASE WHEN table_origin = 'System' THEN 1 ELSE 2 END AS table_code
) AS ca2
WHERE
o.type IN ( 'S', 'U' )
ORDER BY
NEWID() --randomly intermingle Sys and User tables
What's really super sweet is that the "alias" from one ca can be used in a subsequent ca. So you can have cascading "aliases"!
The example below is not practical, of course, it just shows what's possible:
SELECT
ca1.table_origin,
ca2.table_code,
o.*
FROM sys.objects o
CROSS APPLY (
SELECT CASE WHEN o.type = 'S' THEN 'System' ELSE 'User' END AS table_origin
) AS ca1
CROSS APPLY (
SELECT CASE WHEN table_origin = 'System' THEN 1 ELSE 2 END AS table_code
) AS ca2
WHERE
o.type IN ( 'S', 'U' )
ORDER BY
NEWID() --randomly intermingle Sys and User tables
no points please:
Absolutely in agreement with Scott, cross apply provides a very neat alternative.
However just so that the 'old fashioned' way isn't lost to sight, you can simply repeat the case expression in the group by clause so your group by could have been:
Absolutely in agreement with Scott, cross apply provides a very neat alternative.
However just so that the 'old fashioned' way isn't lost to sight, you can simply repeat the case expression in the group by clause so your group by could have been:
GROUP BY
case
when ASC_BR.dbo.MthEndInvtDivVend.Division between 10 and 19 then '10'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 20 and 29 then '20'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 30 and 39 then '30'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 40 and 49 then '40'
when ASC_BR.dbo.MthEndInvtDivVend.Division between 50 and 59 then '50'
else
ASC_BR.dbo.MthEndInvtDivVend.Division
end
, ASC_BR.dbo.MTHENDINVTDIVVEND.Vendornum
, ASC_BR.dbo.VENDOR.Name
ORDER BY
Name, divi
Traditionally column aliases aren't available until the order by clause - as Scott demonstrates cross apply (or outer apply) breaks with that tradition.
Open in new window