SQL query with multiple conditions

SELECT CAST(SUM(t1.TIV_2012) as DECIMAL(10,2))
FROM Insurance t1
INNER JOIN
(
    SELECT TIV_2011
    FROM Insurance
    GROUP BY TIV_2011
    HAVING COUNT(*) > 1
) t2
    ON t1.TIV_2011 = t2.TIV_2011
INNER JOIN
(
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
) t3
    ON t1.lat = t3.lat AND
       t1.lon = t3.lon

SELECT CAST(SUM(t1.TIV_2012) as DECIMAL(11,2)) 
FROM Insurance t1
INNER JOIN (
  SELECT TIV_2011
  FROM Insurance
  GROUP BY TIV_2011 HAVING COUNT(*) > 1 ) t2 ON t1.TIV_2011 = t2.TIV_2011
INNER JOIN ( 
  SELECT lat, lon 
  FROM Insurance 
  GROUP BY lat, lon HAVING COUNT(*) = 1 ) t3 ON t1.lat = t3.lat AND t1.lon = t3.lon

Round(x,2) -> to scale to 2 decimal digits
inner join 1 -> for condition 1 (finds all the repeating TIV_2011)
inner join 2 -> for condition 2 (finds LAT, LON that are distinct as a pair)

Select ROUND(SUM(i1.TIV_2012),2) 
from Insurance i1
inner join 
    (Select TIV_2011 
    from Insurance 
    group by TIV_2011 
    having count(*) > 1) i2 
on
    i1.TIV_2011 = i2.TIV_2011
inner join 
    (Select LAT, LON 
    from Insurance 
    group by LAT, LON 
    having count(*) = 1) i3 
on 
    i1.LAT = i3.LAT 
and  
    i1.LON = i3.LON

Tags:

Mysql

Sql