SUM a column when another column has equal values

Question

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.

Answer 1

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

Tested here

Answer 2

I think you could use something like this. Take the sum and the count of n, and also the min and max (maybe overkill); use that to figure out if all values of n are the same. Along these lines:

SELECT
    CASE WHEN sm/cnt = mn AND sm/cnt = mx THEN sum_area
    ELSE area/cnt END
    AS sum_area
FROM (
    SELECT COUNT(n) cnt, SUM(n) sm, MIN(n) mn, MAX(n) mx, SUM(area_mq) sum_area
    GROUP BY place_id
) a

So, say n has values of 2 and 2 => sm/cnt = mn (4/2 = 2) and mx. If it has values of 2 and 1 => sm/cnt != mn (3/2 != 1).

Answer 3

From what I see in your pattern all with n = 1 is added?

SELECT SUM((n = 1) * area_mq) AS total
FROM table_name

I do a validation that either returns 1 or 0, and then I multiply it with the column value.