Search code examples
mysqlsqlwhere-clauseamazon-aurora

SQL two results from two different where clauses in single query

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?

Solution

  • 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