SQLTeam.com | Weblogs | Forums

Each GROUP BY expression must contain at least one column that is not an outer reference


#1

Hello everyone,
I've written the following code, but I'm receiving the following error in the subject line when I attempt to run. Is there something I'm missing, or do I have something within the wrong place. I've pasted the code below. Any and all help is greatly appreciated:

Select distinct
case
when a.SYS_SBB = 8495 THEN CAST(CONCAT(a.SYS_SBB, a.PRIN_SBB) AS BIGINT)
else a.SYS_SBB
end as 'SYS_PRIN'
,a.[RES_NAME_SBB]
,a.[SUB_ACCT_NO_SBB]
,a.[CUST_ACCT_NO_SBB]
,a.[HSE_KEY_SBB]
,case when a.[EXT_STAT_SBB] = ' ' then 'Active'
when a.[EXT_STAT_SBB] = 'C' then 'Voluntary Disconnect'
when a.[EXT_STAT_SBB] = 'E' then 'Non-Pay Disconnect'
when a.[EXT_STAT_SBB] = 'Z' then 'Charged Off'
else ' ' end as 'Account Status'
,a.[ORIG_CONN_DTE_SBB]
,a.[CONNECT_DTE_SBB]

from ExternalUser.[Vantage].[SBB_BASE] as A (nolock) inner join ExternalUser.[Vantage].[HSE_BASE] as B (nolock)
on a.[SUB_ACCT_NO_SBB] = b.[SUB_ACCT_NO_HSE]
and a.[HSE_KEY_SBB] = b.[HSE_KEY_HSE]

where b.[DWELL_TYP_HSE] = 'HA'

and b.[HSE_KEY_HSE] in (select distinct b.[HSE_KEY_HSE] from ExternalUser.[Vantage].[HSE_BASE]
group by a.SYS_SBB, a.PRIN_SBB, a.[RES_NAME_SBB], a.[SUB_ACCT_NO_SBB], a.[CUST_ACCT_NO_SBB], b.[HSE_KEY_HSE], a.[EXT_STAT_SBB], a.[ORIG_CONN_DTE_SBB], a.[CONNECT_DTE_SBB]
having count (b.[HSE_KEY_HSE]) > 1)


#2

In your subselect, you're grouping by fields from main select.
I would consider using "exists" on this part:

But I need more information on what exactly your query should do.

Please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected result from the sample data you provide

#3

Hi Bitsmed,
Thank you for your quick reply. The tables are already created, so I don't have to use any create statements. I'm not sure what you're requesting when you ask for sample data in the form of insert statements. Lastly, I'm expecting to find multiple active accounts within the same physical location. This is why I'm trying to do a count of HSE_KEY_HSE > 1. HSE_KEY_HSE refers to the locations house key. If there were more than one account within the same physical location, I'm trying to bring these accounts back with the code I've written. Also, can you please elaborate on using "exists" I've actually never used this approach before.


#4

I requested table description and sample data in order for me NOT to spend time to setup a scenario that I can work with, in order to help solve your problem.

exists goes something like this:

where b.[DWELL_TYP_HSE] = 'HA'
  and exists (select 1
                from ExternalUser.[Vantage].[HSE_BASE] as c
               where c.[HSE_KEY_HSE]=b.[HSE_KEY_HSE]
               group by c.[HSE_KEY_HSE]
               having count(*)>1
             )

But this is just me guessing, as I have no idea what your data looks like and can't test syntax.


#5

Hi Bitsmed,
For taking a guess, you did great, because that exists snippet you gave me worked out. Thank you so much for your help!