Get the ratio of a certain column based on other column with different values in SQL?-CodePudding

In this case I am trying to get the ratio of warehouses in KY and warehouses in TN.

| warehouse_id | state |
|     1        | KY    |
|     2        | KY    |
|     3        | TN    |
|     4        | TN    |
|     5        | TN    |
|     6        | FL    |

for example.

I tied the WHERE clause but it limit myself to only one state.

I tried subquery too:

SELECT
   (
     SELECT
       COUNT(DISTINCT warehouse_id)
     FROM table
     WHERE
       state = 'KY'
   ) AS one,

   (
     SELECT COUNT(DISTINCT warehouse_id)
     FROM table
     WHERE
       state = 'TN'
   ) AS two,
  
   one/two as ratio
   
FROM table

CodePudding user response：

You can combine COUNT with CASE statement in same query:

SELECT 
    COUNT(DISTINCT CASE WHEN state = 'KY' THEN warehouse_id END) / 
    COUNT(DISTINCT CASE WHEN state = 'TN' THEN warehouse_id END) AS ratio
FROM w;

Test it here: https://sqlize.online/sql/mysql57/240255e9751046d9d1306320befddd09/

In PostgreSQL you can use FILTER like:

SELECT 
    (COUNT(DISTINCT warehouse_id) FILTER ( WHERE state = 'KY' ))::NUMERIC / 
    (COUNT(DISTINCT warehouse_id) FILTER ( WHERE state = 'TN' ))::NUMERIC
    AS ratio
FROM w;

with same result

https://sqlize.online/sql/psql15/66b76627b81c2902ec1ed30b3f785d2d/

CodePudding user response：

In SQL Server you need to cast your denominator as float.

SELECT 2 / CAST( 3 AS FLOAT);