MySQL how to select 1 if MAX() equals current row SUM() aggregate value?

Question

I'd like to select a new column named sliced (value can be 1/0 or true/false it doesn't matter) if area of the current row equals MAX(SUM(c.area)), that is flag the row with highest aggregate value:

SELECT p.name AS name, SUM(c.area) AS area
FROM City AS c
   INNER JOIN Province AS p ON c.province_id = p.id
      INNER JOIN Region AS r ON p.region_id = r.id
WHERE r.id = ?
GROUP BY p.id
ORDER BY p.name ASC

I've tried adding to the selection area = MAX(area) AS sliced or even area = SUM(MAX(c.area)) AS sliced but i'm getting a syntax error. I've to admit i'm not so good in SQL. Thank you.

Answer 1

As I understand your question, this should do it. Creates a pseudo-column which returns 1 when the area is the same as max(area) without any conditions to restrict your selection.

SELECT name
     , area
     , case area when max_area then 1 else 0 end as sliced
  FROM ( SELECT name
              , area
              , max(area) over (partition by 1) as max_area
           FROM ( SELECT p.name AS name
                       , SUM(c.area) AS area
                    FROM City AS c
                   INNER JOIN Province AS p ON c.province_id = p.id
                   INNER JOIN Region AS r ON p.region_id = r.id
                   WHERE r.id = ?
                   GROUP BY p.id
                   ORDER BY p.name ASC )
               )

EDIT As @Glide says you can't perform nested aggregation so sum(max(area)) won't work and you need to perform these operations one query at a time.