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);