CODEWITHSUNDEEP
mysqlsqlgoogle-bigquery
shaylevi2
shaylevi2

Reputation: 672

Two SQL counts without nested select

I have an SQL query I need to change not to be nested selects.

I'm not sure how to do it....

SELECT 
    (SELECT COUNT(DISTINCT _user_id) FROM [data] WHERE (_language == 'en')) / 
    COUNT(DISTINCT _user_id)
FROM [data]

Preferably not with 'JOIN' (unless there's no other way).

EDIT: I need the ratio between users who uphold the condition and those who don't

Upvotes: 2

Views: 106

Answers (2)

Mureinik
Mureinik

Reputation: 311498

You could use an inline case expression:

SELECT COUNT(DISTINCT CASE WHEN _language = 'en' THEN _user_id ELSE NULL END) / 
       COUNT(DISTINCT _user_id)
FROM   [data]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this with conditional aggregation:

SELECT (COUNT(DISTINCT (CASE WHEN _language = 'en' THEN _user_id END)) /
        COUNT(DISTINCT _user_id)
       )
FROM data;

The question is tagged MySQL, and yet you are using square braces -- this will cause an error in MySQL. You don't need the square braces anyway. The standard equality comparison is =, not == (even if bigquery supports both).

Upvotes: 3

Related Questions