Select SQL statement on same column

Question

I want to select distinct count(id) as activeusers from a table where useractive=1, but at the same time I want to select all the users example distinct Count(id) as totalusers regardless of activestate. How to achieve this in a SQL query?

Answer 1

You could call count over a case expression:

SELECT COUNT(DISTINCT CASE useractive WHEN 1 THEN id END) AS active_users,
       COUNT(DISTINCT id) AS total_users
FROM   mytable