Group functions of outer query inside inner query

Question

Hello,

I'm using Oracle. I'm trying to compose something like this:

SELECT trans_type,
(SELECT parameter_value FROM transaction_details WHERE id = MAX(t.trans_id))
FROM
(SELECT trans_id, trans_type FROM transactions) t
GROUP BY trans_type

So, I am trying to use a result of grouping inside an inner query. But I am getting the error that I cannot use a group function inside the inner query:

ORA-00934: group function is not allowed here

Can you offer an alternative other than resorting to another outer query?

UPDATE

As requested, I am posting a shorter version of the real query:

  SELECT service_code,
         currency,
         (SELECT value FROM exchange_rate WHERE date_added = MIN(t.trans_date)) AS exchange_rate,
         TRIM(TO_CHAR(SUM(amount), '9999999990.99')) AS amount,
         TRIM(TO_CHAR(SUM(tax_amount), '9999999990.99')) AS tax_amount,
         TRIM(TO_CHAR(SUM(total_amount), '9999999990.99')) AS total_amount
    FROM (SELECT t.amount AS amount,
                t.trans_date AS trans_date
                 t.tax_amount AS tax_amount,
                 t.total_amount AS total_amount,
                 td1.string_value AS service_code,
                 td2.string_value AS currency
            FROM transac) t
GROUP BY service_code, currency

Answer 1

What's wrong with a more traditional query:

SELECT t.trans_type, td.parameter_value, MAX(t.trans_id)
  FROM transactions t
 INNER 
  JOIN transaction_details td ON td.id = t.trans_id
 GROUP BY t.trans_type, td.parameter_value

Answer 2

I think what you're looking for is something like this:

  SELECT service_code,
         currency,
         er.value as exchange_rate,
         TRIM(TO_CHAR(SUM(amount), '9999999990.99')) AS amount,
         TRIM(TO_CHAR(SUM(tax_amount), '9999999990.99')) AS tax_amount,
         TRIM(TO_CHAR(SUM(total_amount), '9999999990.99')) AS total_amount
    FROM (SELECT t.amount AS amount,
                 t.tax_amount AS tax_amount,
                 t.total_amount AS total_amount,
                 td1.string_value AS service_code,
                 td2.string_value AS currency,
                 min(t.trans_date) 
                     over (partition by td1.string, 
                                        td2.string) as min_trans_date
            FROM transac t, table2 td1, table3 td2) t,
          exchange_rate er
   WHERE er.date_added = t.min_trans_date

I'm not sure that this is really you're solution, because, based on your query, I can't tell what group you want the minimum transaction date to be based on. This version assumes that you want the earliest transaction date for a given combination of service code and currency.

You're running into a couple problems with your approach:

1. Any column in the SELECT clause needs to either be in the GROUP BY clause or subject to a aggregate function. You have several columns that fail this criteria.

2. You're trying to use an aggregate function from the outer query in the scalar subquery. You just can't do that. At best, you can get the aggregate in a nested query, then reference that value later.