SQL GROUP BY only in subquery

Question

I have a set of datapoints for the number of fans of different accounts for different days belonging to different brands:

|brand|account|date|fans| 
|-----|-------|----|----|
|Ford |ford_uk|... |10  |
|Ford |ford_uk|... |11  |
|Ford |ford_us|... |20  | 
|Ford |ford_us|... |21  | 
|Jeep |jeep_uk|... |30  |
|Jeep |jeep_uk|... |31  |
|Jeep |jeep_us|... |40  |
|Jeep |jeep_us|... |41  |

I'm trying to return the total fans by brand, defined as the sum of the max fans for each of the brand's accounts:

Ford: 32
Jeep: 72

I tried a subquery like this:

(SELECT sum(account_fans)
  FROM
  (
    SELECT max(fans) AS account_fans
    GROUP BY account
  ) subquery_name
) AS total_fans

The problem is that I get:

ERROR: subquery uses ungrouped column account from outer query.

But I don't want to group the outer query. Can you help?

Answer 1

Have you tried writing your query this way?

select  brand, sum(mx)
from    (
            select  brand, account, max(fans) mx
            from    account_fans
            group by brand, account
        ) t1
group by brand

Answer 2

You need two levels of subqueries:

select brand, sum(fans)
from (select brand, account, max(fans) as fans
      from account_fans af
      group by brand, account
     ) ba
group by brand;