Solved

Restructure SQL Query To Use Column Alias in GROUP BY

Posted on 2013-11-05
8
382 Views
Last Modified: 2013-11-05
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

Open in new window

0
Comment
Question by:trbbhm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39625724
If you don't want to use your CASE in the ORDER BY, then you should be able to do that with a subquery:

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,
	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 
) X
	order by Name,divi

Open in new window

0
 

Author Comment

by:trbbhm
ID: 39625755
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:

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

Open in new window


I now get the following error message from the inside SELECT statement:

Column 'ASC_BR.dbo.MTHENDINVTDIVVEND.Division' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39625807
CROSS APPLY works great for this!  See example below.  btw, "ca1" stands for simply "cross apply 1".


select
      ca1.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
      cross apply (
          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
      ) as ca1
     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 ca1.divi,ASC_BR.dbo.MTHENDINVTDIVVEND.Vendornum,ASC_BR.dbo.VENDOR.Name
      order by Name,divi
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
LVL 11

Expert Comment

by:Simone B
ID: 39625810
So sorry, I misread the question. Because your aggregate functions are in the sub-select, your GROUP BY has to stay there.
0
 

Author Closing Comment

by:trbbhm
ID: 39625811
That is so far over my head, but thank you!!!
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39625813
Scott, just read your comment. That's nice!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39625843
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39626240
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:
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

Open in new window

Traditionally column aliases aren't available until the order by clause - as Scott demonstrates cross apply (or outer apply) breaks with that tradition.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question