Geek Answers Handbook

SQL query with multiple conditions

I am stuck on this issue. Can anyone help?

Write a request to print the sum of all total investment values ​​in 2015 (TIV_2012) up to a scale of 2 decimal places for all policyholders that meet the following criteria:

1) Have one value TIV_2011 as one or more other policy holders.

2) Not located in the same city as another policy holder (i.e. the paragraph of the attributes of latitude, longitude) must be unique,

input format is similar to this one, table

The insurance table is described as follows:

Column name PID INTEGER TIV_2011 NUMBER TIV_2012 NUMBER LAT NUMERIC LON NUMERIC

where PID is the identifier of the policy holder, TIV_2011 is the total investment in 2011, TIV_2012 is the total investment in 2012, LAT is the latitude of the policy holder city, and LON is the longitude of the policy holder city.

For example, if the data is PID, TIV_2011, TIV_2012, lat, lon

  • 1, 300, 400.5, 60, 70

  • 2, 300, 500.7, 70, 80

  • 3, 400, 400, 60, 90

  • 4, 500, 600, 80, 80

  • 5, 400, 300.1, 6, 6

The answer will be 1601.30. Amount (300.1, 400, 500.7, 400.5)

So far I came up with this

CHOOSE AMOUNT (TIV_2012) FROM INSURANCE, WHEN IT IS NOT UNIQUE (CHOOSE TIV_2011 from insurance);

This does not work, I get an error. Someone can help.

+7
source share
6 answers
SELECT SUM(t1.TIV_2012) 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
+12
source 
 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
+3
source 
 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
+1
source

I believe that the query you need to write has two internal joins and looks something like this:

 SELECT SUM(ins1.column_of_interest) as value_needed FROM Insurance ins1 INNER JOIN Insurance ins2 ON (ins1.id = ins2.id AND <<conditions to get same TIV_2011 applied to ins2 >> ) INNER JOIN Insurance ins3 ON (ins1.id = ins3.id AND << conditions to get unique latitude, longitude on ins3 >> ) WHERE << other conditions you may apply to ins1 >>
0
source

All the answers here are in one approach. I tried a different approach, so post it.

 SELECT CAST(SUM(i.TIV_2012) AS NUMERIC(18,2)) FROM Insurance i WHERE i.PID IN (SELECT DISTINCT(i1.PID) FROM Insurance i1 INNER JOIN Insurance i2 ON (i1.TIV_2011 = i2.TIV_2011 AND i1.PID <> i2.PID) WHERE NOT EXISTS (SELECT i3.PID FROM Insurance i3 WHERE i1.PID <> i3.PID AND i1.LAT = i3.LAT AND i1.LON = i3.LON));
0
source 
 SELECT SUM(i1.TIV_2012) FROM ( SELECT * ,COUNT(1) OVER (PARTITION BY i.TIV_2011 ORDER BY i.TIV_2011) R2 ,COUNT(1) OVER (PARTITION BY i.Lat, i.Lon ORDER BY i.Lat, i.Lon) R1 FROM #Insurance i) i1 WHERE R2 > 1 AND R1 = 1
0
source

More articles:


All Articles