SQL two results from two different where clauses in single query [duplicate]

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
– James, Commented Oct 13, 2022 at 13:24

Kota Mori · Accepted Answer · 2022-10-13 12:45:28Z

1

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

answered Oct 13, 2022 at 12:45

Kota Mori

6,7621 gold badge25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL two results from two different where clauses in single query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related