CODEWITHSUNDEEP
mysqlsql
MyCoy
MyCoy

Reputation: 61

Correct approach to a SQL query

I need help on a MySQL based SQL problem, the following is description and sample input:

enter image description here

And the following is sample output:

enter image description here

I've tried something like:

select ROUND( sum(i1.TIV_2012), 2 ) from INSURANE i1, INSURANCE i2, INSURANCE i3 where ( i1.PID!=i2.PID and i1.TIV_2011=i2.TIV_2011 ) and (i1.PID!=i3.PID and (i1.LAT!=i3.LAT or i1.LON!=i3.LON) )

But the system cannot accept this. Could anyone help me out?

Upvotes: 0

Views: 815

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Use the following SQL

SELECT SUM(i1.tiv_2012) total       
  FROM insurance i1
 WHERE NOT EXISTS (SELECT 1 
                     FROM insurance i2 
                    WHERE i1.lat = i2.lat 
                      AND i1.lon = i2.lon 
                      AND i1.pid <> i2.pid)
   AND EXISTS (SELECT i3.tiv_2011 
                 FROM insurance i3 
                WHERE i3.tiv_2011 = i1.tiv_2011 
                  AND i3.pid <> i1.pid);

Result

total
1711758.65

Upvotes: 1

Related Questions