tsql - Query throws exception in SQL Server but not MySQL (outer reference error) -

sql not strong suit pretty happy when came query. works fine when querying mysql database gives me error in sql server database. exact error is:

each group expression must contain @ least 1 column not outer reference.

i googled error , tried adjust query accordingly can't seem work. i've been stuck on week. give me pointers on it? maybe there better way this? server sql server 2012 btw if means anything.

it should showing results below.

------------------------------------------------------------------------------------- category    |total customer purchases|monthly limit|customer id|last transaction date| ------------------------------------------------------------------------------------- free items  | 12                     | 10          | 635       | 2014-03-01 food items  | 03                     | 10          | 635       | 2014-03-01 other items | 05                     | 10          | 635       | 2014-03-01 

here's query:

select (select product_categories.cat_name           product_categories          product_categories.cat_id = transaction_details.fk_category_id)               'category',        sum(transaction_details.quantity)        'total customer purchases',        (select product_categories.cat_limit_for_month           product_categories          product_categories.cat_id = transaction_details.fk_category_id)               'monthly limit',        transactions.cust_id        'customer id',        transactions.trans_date        'last transaction date'   transactions        left join transaction_details               on transactions.link_to_trans_detail =                  transaction_details.link_to_transactions  transactions.cust_id = '25'        , transactions.trans_date >= '2014-03-01 19:25:44.000' group  'category' having transactions.trans_date >= '2014-03-01 19:25:44.000'  

a couple improvements:

1. use aliases tables. easier type , intellisense you, too.
2. include product_categories table in clause.
3. eliminate subqueries in select statement. though can work, won't perform @ scale in cases.
4. consider using brackets around column names instead of apostrophes if have spaces. intellisense play better in many circumstances. or remove spaces altogether.
5. you don't need having clause if restricted data in clause.
6. is there reason used left join? if data must exist on both sides, consider changing join. if data isn't there, consider adding isnull() handling category field avoid nulls.
7. i recommend putting columns intend group next each other in select statement make code intent clearer. makes obvious data granularity supposed be.
8. assuming ad hoc or report query, may want consider using nolock table hints if have low risk of dirty reads avoid blocking or being blocked queries.

here's sample version of mean:

select pc.cat_name category,        t.cust_id [customer id],        sum(td.quantity) [total customer purchases],        max(pc.cat_limit_for_month) [monthly limit],        max(t.trans_date) [last transaction date] transactions t (nolock) left join transaction_details td (nolock)     on t.link_to_trans_detail = td.link_to_transactions left join product_categories pc (nolock)     on td.fk_category_id = pc.cat_id t.cust_id = '25' , t.trans_date >= '2014-03-01 19:25:44.000' group pc.cat_name     , t.cust_id;