SUM column when another column has equal values

Question

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 is the identifier of the area, area_mq is the surface area of ​​the place in mq.

I need to find a compact query to calculate the area_mq sum using these rules:

• If n is equal for the same place_id , then count all area_mq (f.ex for place_id = 2, I count 90 and 20 in total)
• If n is different for the same place_id , then count area_mq only once (perhaps because for these "places" the value will be the same; f.ex. place_id = 4, there are two 20, I summarize only 20, not 40).

In this example, my correct amount will be: 50 + (90 + 20) + 10 + 20 + (15 + 25).

Can I do this with a query (without code or procedures)? If the requirements for n were canceled, that would be simple with 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

From what I see in your template, everything with n = 1 added?

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

I perform a check that returns 1 or 0, and then multiply it by the column value.

Answer 3

I think you could use something like this. Take the sum and quantity of n , as well as min and max (possibly an excess); use this to find out if all n values ​​match. In 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, let's say n has values ​​2 and 2 => sm / cnt = mn (4/2 = 2) and mx. If it has values ​​2 and 1 => sm / cnt! = Mn (3/2! = 1).