Question: The data in my table looks like this:

```
date, app, country, sales
2017-01-01,XYZ,US,10000
2017-01-01,XYZ,GB,2000
2017-01-02,XYZ,US,30000
2017-01-02,XYZ,GB,1000

```

I need to find, for each app on a daily basis, the ratio of US sales to GB sales, so ideally the result would look like this: 

```
date, app, ratio
2017-01-01,XYZ,10000/2000 = 5
2017-01-02,XYZ,30000/1000 = 30

```

I'm currently dumping everything into a csv and doing my calculations offline in Python but I wanted to move everything onto the SQL side. One option would be to aggregate each country into a subquery, join and then divide, such as

```
select d1_us.date, d1_us.app, d1_us.sales / d1_gb.sales from
(select date, app, sales from table where date between '2017-01-01' and '2017-01-10' and country = 'US') as d1_us
join 
(select date, app, sales from table where date between '2017-01-01' and '2017-01-10' and country = 'GB') as d1_gb
on d1_us.app = d1_gb.app and d1_us.date = d1_gb.date

```

Is there a less messy way to go about doing this?

Answer: 
You can use the ratio of SUM(CASE WHEN) and GROUP BY in your query to do this without requiring a subquery. 

```
SELECT DATE, 
       APP,
       SUM(CASE WHEN COUNTRY = 'US' THEN SALES ELSE 0 END) /
       SUM(CASE WHEN COUNTRY = 'GB' THEN SALES END) AS RATIO    
FROM TABLE1
GROUP BY DATE, APP;

```

Based on the likelihood of the GB sales being zero, you can tweak the GB's ELSE condition, maybe `ELSE 1`, to avoid Divide by zero error. It really depends on how you want to handle exceptions.
You can use one query with grouping and provide the condition once:

```
SELECT date, app,
       SUM(CASE WHEN country = 'US' THEN SALES ELSE 0 END) /
       SUM(CASE WHEN country = 'GB' THEN SALES END) AS ratio
WHERE date between '2017-01-01' AND '2017-01-10'
FROM your_table
GROUP BY date, app;

```

However, this gives you zero if there are no records for US and `NULL` if there are no records for GB. If you need to return different values for those cases, you can use another `CASE WHEN` surrounding the division. For example, to return -1 and -2 respectively, you can use:

```
SELECT date, app,
       CASE WHEN COUNT(CASE WHEN country = 'US' THEN 1 ELSE 0 END) = 0 THEN -1
            WHEN COUNT(CASE WHEN country = 'GB' THEN 1 ELSE 0 END) = 0 THEN -2
            ELSE SUM(CASE WHEN country = 'US' THEN SALES ELSE 0 END) /
                 SUM(CASE WHEN country = 'GB' THEN SALES END)
            END AS ratio
WHERE date between '2017-01-01' AND '2017-01-10'
FROM your_table
GROUP BY date, app;

```