SQL two results from two different where clauses in single query

Question

I would like to count the number of observations in a where clause, and then divide that result by the result of a count with a different where clause. Is that possible in a single query? Both operations are on the same table.

I've tried this so far:

SELECT 
  filter(COUNT(DISTINCT table.column1) WHERE table.column2 <> 'var_1') / 
  filter(COUNT(DISTINCT table.column1) WHERE table.column2 == 'var_2')
FROM table

and

SELECT result1 / COUNT(DISTINCT table.column1) AS result2
FROM
(
SELECT COUNT(DISTINCT table.column1) AS result1
FROM table
WHERE table.column2 <> 'var_1'
) AS inner_query
WHERE table.column2 <> 'var_2'

With very little success. Any suggestions?

Thanks, not that's perfect. I had no idea that you could tag the database type, but that's useful to know. — James, Oct 13 '22 at 13:24

score 1 · Accepted Answer · answered Oct 13 '22 at 12:45

In many SQL engine, you can do something like below. Define the numerator and the denominator in separate tables and join together.

SELECT
  a.result1 / b.result2
FROM
  ( SELECT COUNT(DISTINCT column1) AS result1 
    FROM table
    WHERE table.column2 <> 'var_1' ) AS a,
  ( SELECT COUNT(DISTINCT column1) AS result2
    FROM table
    WHERE table.column2 == 'var_2' ) AS b

SQL two results from two different where clauses in single query

1 Answers1