Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query with multiple conditions

Tags:

sql

mysql

I am stuck on this question. Can anyone help?

Write a query to print the sum of all total investment values in 2015 (TIV_2012) toa scale of 2 decimal places, for all policyholders who meet the following criteria:

1) Have the same TIV_2011 value as one or more other policyholders.

2) Are not located in the same city as another policy holder (i.e. (latitude, longitude) attribute pair must be unique,

the input format islike this , The table is

Insurance table is described as follows:

Column Name Type PID INTEGER TIV_2011 NUMERIC TIV_2012 NUMERIC LAT NUMERIC LON NUMERIC

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

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

  1. 1, 300, 400.5, 60, 70

  2. 2, 300, 500.7, 70, 80

  3. 3, 400, 400, 60, 90

  4. 4, 500, 600, 80, 80

  5. 5, 400, 300.1, 6, 6

The answer would be 1601.30. Sum of (300.1, 400, 500.7, 400.5)

So, far I have come up with this

SELECT SUM(TIV_2012) FROM Insurance WHERE NOT UNIQUE(SELECT TIV_2011 from Insurance);

This does not work, I am getting an error. Someone pls help.

like image 255
sqlnoob Avatar asked Oct 14 '16 21:10

sqlnoob


People also ask

How do I pass multiple conditions in SQL query?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.

Can we have multiple with in a SQL query?

To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.

Can we use two conditions in case statement in SQL?

Example 1: Multiple Conditions using AND For our example: condition_1 AND condition_2: age >= 60 AND member = 'Yes'

How do you write multiple conditions?

You can use the OR condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met. This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition.


2 Answers

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
like image 179
Tim Biegeleisen Avatar answered Nov 05 '22 21:11

Tim Biegeleisen


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
like image 23
Gaurav Kasliwal Avatar answered Nov 05 '22 21:11

Gaurav Kasliwal