CODEWITHSUNDEEP
sqlsql-server
user2063626
user2063626

Reputation:

SQL Query Issue Cannot perform an aggregate function on an expression containing an aggregate or a subquery99

enter image description here The above query works when i remove the subquery. but i want the amount to be multiplied by notional rate from table n_bank_rate as specified in query. How can this be achieved ?

     select 2 as orderby, 2 as n_order2,null as exp_id ,null as company,
         'Total -' + fac.facility_type as cashflow,
         null as exp_identification_date,
         bk.bank_name as bank,null as branch,
         null as Counterparty,null as country,null as facility_type,
         bank_facility_id as bank_facility_id ,  null as curr1, 
          sum(m.amount) as curr1_amt,
         null as curr2,
         sum(m.amount * (select top 1 notional_rate from n_bank_rate where bank_id = m.bank_id order by id desc)) as curr2_amt,
         null as converstion_rate,null as due_date,null as bank_ref,null as invoice_no,null as remarks ,
         null as [term],null as [terms_type] 
        from m_forex_exposure m 
        join m_company cm on m.comp_id = cm.comp_id
        left join m_bank bk on m.bank_id = bk.id
        left join m_facility_type fac on m.bank_facility_id = fac.id 
        join n_link_exposure le on  m.exp_id = le.ref_exp_id 
        where 
        (cm.comp_main_id = 1 and cm.group_id =1 )
        and m.amount > 0
        and (m.bank_id =94) 
        and isnull(m.bank_id,0) <> 0
        and bank_facility_id in (select id as bank_facility_id from m_facility_type where facility_type  in ('EBRD','PCFC'))
        group by  bk.bank_name , bank_facility_id,fac.facility_type

Upvotes: 0

Views: 349

Answers (2)

Mortalus
Mortalus

Reputation: 10712

As sql server tells you... you cannot run an aggregation why there is a sub query subquery simply select again from the whole thing after the sub-query is done.

SELECT 
sum(curr1_amt), 
sum(curr1_amt)*curr2_amt,
orderby,
n_order2,
exp_id ,
company,
cashflow,
exp_identification_date,
bank,
branch,
Counterparty,
country,
facility_type,
bank_facility_id ,
curr1,
curr2,
converstion_rate,
due_date,
bank_ref,
invoice_no,
remarks ,
[term],
[terms_type] 
FROM
(
    SELECT
    2 as orderby,
    2 as n_order2,
    null as exp_id ,
    null as company,
    'Total -' + fac.facility_type as cashflow,
    null as exp_identification_date,
    bk.bank_name as bank,
    null as branch,
    null as Counterparty,
    null as country,
    null as facility_type,
    bank_facility_id as bank_facility_id ,
    null as curr1,
    m.amount as curr1_amt,
    null as curr2,
    curr2_amt = (select top 1 notional_rate from n_bank_rate where bank_id = m.bank_id),
    null as converstion_rate,
    null as due_date,
    null as bank_ref,
    null as invoice_no,
    null as remarks ,
    null as [term],
    null as [terms_type]           

    FROM 
    m_forex_exposure m   
    join m_company cm on m.comp_id = cm.comp_id          
    left join m_bank bk on m.bank_id = bk.id          
    left join m_facility_type fac on m.bank_facility_id = fac.id           
    join n_link_exposure le on  m.exp_id = le.ref_exp_id           

    WHERE
    (cm.comp_main_id = 1 and cm.group_id =1)          
    and m.amount > 0          
    and (m.bank_id =94)           
    and isnull(m.bank_id,0) <> 0          
    and bank_facility_id in (select id as bank_facility_id 
                     from m_facility_type 
                     where facility_type  in ('EBRD','PCFC')
                         )          
) AS mytable
GROUP BY
orderby,
n_order2,
exp_id ,
company,
cashflow,
exp_identification_date,
bank,
branch,
Counterparty,
country,
facility_type,
bank_facility_id ,
curr1,
curr2,
converstion_rate,
due_date,
bank_ref,
invoice_no,
remarks ,
[term],
[terms_type]

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Also in SQLServer2005+ for this purpose you can use CROSS APPLY

 select 2 as orderby, 2 as n_order2,null as exp_id ,null as company,
         'Total -' + fac.facility_type as cashflow,
         null as exp_identification_date,
         bk.bank_name as bank,null as branch,
         null as Counterparty,null as country,null as facility_type,
         bank_facility_id as bank_facility_id ,  null as curr1, 
          sum(m.amount) as curr1_amt,
         null as curr2,
         sum(m.amount * curr2_amt.notional_rate) as curr2_amt,
         null as converstion_rate,null as due_date,null as bank_ref,null as invoice_no,null as remarks ,
         null as [term],null as [terms_type] 
        from m_forex_exposure m 
        join m_company cm on m.comp_id = cm.comp_id
        left join m_bank bk on m.bank_id = bk.id
        left join m_facility_type fac on m.bank_facility_id = fac.id 
        join n_link_exposure le on  m.exp_id = le.ref_exp_id 
        CROSS APPLY (select top 1 notional_rate from n_bank_rate where bank_id = m.bank_id order by id desc) as curr2_amt (notional_rate) 
        where 
        (cm.comp_main_id = 1 and cm.group_id =1 )
        and m.amount > 0
        and (m.bank_id =94) 
        and isnull(m.bank_id,0) <> 0
        and bank_facility_id in (select id as bank_facility_id from m_facility_type where facility_type  in ('EBRD','PCFC'))
        group by  bk.bank_name , bank_facility_id,fac.facility_type

Upvotes: 0

Related Questions