CODEWITHSUNDEEP
sqlsqlitecommon-table-expression
user21200015
user21200015

Reputation:

Computing ratio with two tables with multiple conditions

Table A

date flight airport
2012-10-01 oneway ATL, GA
2012-10-01 oneway LAX, CA
2012-10-01 oneway SAN, CA
2012-10-01 oneway DTW, MI
2012-10-02 round SFO, CA

Table B

date temp precip
2012-10-01 67 0.02
2012-10-01 65 0.32
2012-10-01 86 0.18
2012-10-01 87 0.04
2012-10-02 78 0.24

The actual tables have more than 100k rows.

Exepected outcome has two columns temp and ratio

For each temp, I am trying to get the a ratio of flight = oneway where airport have "CA" in it. I need to first filter rows that the average of precip is greater than 0.2 and cast ratio to interger.

I tried to join on date and group by temp that is having average precip < 0.2 but I am getting fixed wrong value on ratio.

How can I do CTE or CASE WHEN to merge these two tables to compute ratio?

Ratio is should be the (total count of all rows where flight = 'oneway' per each temperature after all filtering) / (total counts of rows)

Upvotes: 0

Views: 93

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76436

In the query below I join A and B records over their Date matching as well as B.airport ending with CA, grouped by temperature. The total number of such pairs is the result of COUNT(*) that I divide with. The value I am dividing is the number of items from the pairs which have a oneway flight. It's possible that I did not fully understand the question, in which case we may need to move the airport criteria from the where into the case-when.

SELECT b.temp,
       CAST(SUM(
           CASE
               WHEN A.flight = 'oneway'
               THEN 1
               ELSE 0
           END
       ) AS FLOAT) / COUNT(*)
FROM A
JOIN B
ON A.`Date` = B.`Date` AND
   B.airport LIKE '%CA'
GROUP BY B.temp

Upvotes: 1

Related Questions