Problem with grouping by an alias

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 2008 Forums

Other SQL Server 2008 Topics

Problem with grouping by an alias

Author

Topic

DB Analyst
Starting Member

8 Posts

Posted - 2011-02-23 : 10:02:39

Hi everyone,

In the below query I'm working on, I'm trying to get 3 things from all the joined tables: the CUSTOMER_ACCOUNT_ID, the transaction date in the form (YYYYMM), and the sum of the total transaction amounts.

Note the GROUP BY statement as the second-to-last line. SQL Server does not seem to like the "PERIOD" variable, although it is referenced in the SELECT statement. The error indicates, "Invalid column name".

Any help you can give me regarding this problem would be appreciated. Thanks!

*******************************************

select cal.customer_account_id
,(SELECT YEAR(txn_date)*100 + MONTH(txn_date)) AS PERIOD
,sum(txn_amount_base)
from transactions t
join customer_account_link cal
on t.account_id = cal.account_id
and txn_id in (
select txn_id
from transactions t
join keyword k
on UPPER(t.customer_name) like '%' + k.keyword + '%'
or UPPER(t.originator_name) like '%' + k.keyword + '%')
group by cal.customer_account_id, PERIOD
having sum(txn_amount_base) > 750

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-23 : 10:38:57

group by cal.customer_account_id, YEAR(txn_date)*100 + MONTH(txn_date)

No, you're never too old to Yak'n'Roll if you're too young to die.

DB Analyst
Starting Member

8 Posts

Posted - 2011-03-03 : 11:04:48

OK. I did it slightly differently, and it worked.

CONVERT(CHAR(6), TXN_DATE, 112) AS PERIOD

and then

group by CONVERT(CHAR(6), TXN_DATE, 112)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources