SUM a column when another column has equal values

mysql

Solution

select place_id, sum(area_sq)/count(distinct n)
from your_table
group by place_id;

Tested here

Problem

Let's suppose I have this MySQL table: ``` id place_id area_mq n -- -------- ------- -- 1 1 50 1 2 2 90 1 3 2 20 1 4 3 10 1 5 3 10 2 6 3 10 3 7 4 20 1 8 4 20 2 9 5 15 1 10 5 25 1 ``` `id` is the primary key, `place_id` stands for an area id, `area_mq` is the surface area of the place in mq. What I have to do is find a compact query to calculate the sum of `area_mq` with these rules: - if `n` is equal for the same `place_id`, then count every `area_mq` (f.ex for `place_id`=2, I count 90 and 20 in the sum) - if `n` is different for the same `place_id`, then count `area_mq` only once (possible to do because for these kind of "places" the value will be the same; f.ex. `place_id`=4, there are two 20's, i sum only 20 and not 40). In this example, my correct total would be: 50 + (90 + 20) + 10 + 20 + (15 + 25). Can I accomplish this with a query (no code or procedures)? If requirements for n were reversed, it would be simple with a GROUP BY and a subquery... but with these conditions, I'm stuck.

Original source