Get another column from sum-sub-select

I'm selecting something from a sub-select, which in turn gives me a list of sums. Now I want to select the base_unit column, which contains the unit of measurement. I can't seem to add base_unit to the sub-select because then it doesn't work with the GROUP BY statement.

SELECT to_char(a.pow * f_unit_converter(base_unit, '[W]'), '000.00') 
FROM (
    SELECT sum (time_value) AS pow 
    FROM v_value_quarter_hour
    WHERE 
       mp_id IN (SELECT mp_id FROM t_mp WHERE mp_name = 'AC') AND 
       (now() - time_stamp < '5 day') 
    GROUP BY time_stamp
    ORDER BY time_stamp DESC 
) a 
LIMIT 1

Where/how can I additionally select the ba开发者_StackOverflow社区se_unit from the t_mp Table for each of those sums, so that I can pass it to the f_unit_converter function?

Thanks a lot, MrB

SELECT to_char(a.pow * f_unit_converter(a.base_unit, '[W]'), '000.00') 
FROM (
    SELECT sum (time_value) AS pow, t_mp.base_unit
    FROM v_value_quarter_hour
        inner join t_mp on (v_value_quarter_hour.mp_id = t_mp.mp_id)
    WHERE 
       t_mp.mp_name = 'AC' AND 
       (now() - time_stamp < '5 day') 
    GROUP BY time_stamp, base_unit
    ORDER BY time_stamp DESC 
) a 
LIMIT 1

Assuming that all your selected rows have the same base_unit, you should be able to add it both to the SELECT and the GROUP BY of your sub-query.

Use an INNER JOIN instead of an IN. Something like this

SELECT to_char(a.pow * f_unit_converter(base_unit, '[W]'), '000.00')  FROM (
    SELECT sum (time_value), base_unit AS pow 
    FROM v_value_quarter_hour
    INNER JOIN t_mp ON v_value_quarter_hour.mp_id = t_mp.mp_id
    WHERE mp_name = 'AC' AND 
       now() - time_stamp < '5 day'
    GROUP BY time_stamp, base_unit
    ORDER BY time_stamp DESC  ) a  LIMIT 1