is invalid in the select list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

is invalid in the select list

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-25 : 18:30:25

[code]select L.EMP_ID as EMP_ID,
E.LST_NM as LAST_NAME,
TENDER_TOTAL=sum(L.AMT),
0 as DISCREPENCY,
' ' as REASON ,
'Cash' as TENDER_TYPE
from LOAN L inner join EMPLOYEE E
on (E.EMP_ID = L.EMP_ID)[/code]
How to modify the sql?
Thanks
[code]Column 'EMPLOYEE.LST_NM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 19:10:00

you need to include a GROUP BY clause:


select	L.EMP_ID              as EMP_ID,
	E.LST_NM              as LAST_NAME,
        TENDER_TOTAL=sum(L.AMT),
	0                     as DISCREPENCY, 
	' '                   as REASON , 
        'Cash'                as TENDER_TYPE  
from  LOAN L inner join EMPLOYEE E
		on (E.EMP_ID = L.EMP_ID)
GROUP BY L.EMP_ID
	 ,E.LST_NM

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:35:02

or use like this


select   DISTINCT L.EMP_ID              as EMP_ID, 
			 E.LST_NM              as LAST_NAME,
        TENDER_TOTAL=sum(L.AMT) OVER (PARTITION BY L.EMP_ID,E.LST_NM),
       0    as DISCREPENCY, 
	   ' '                         as REASON , 
        'Cash'               as TENDER_TYPE  
from  LOAN L inner join EMPLOYEE E
					 on (E.EMP_ID = L.EMP_ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources