CODEWITHSUNDEEP
sqlcount
user370306
user370306

Reputation:

Select two counts in one query

I have users(user,pass,email) table and I want to get the count of user where user='someuser' and the count of email where email='someemail' in one query and I came up with that:

SELECT (

SELECT COUNT( user )
FROM users
WHERE user = 'someuser'
), (

SELECT COUNT( email )
FROM users
WHERE email = 'someemail'
)
FROM users

But I'm wondering if there is a better way of doing that? Thanks in advance :)

Upvotes: 5

Views: 3731

Answers (1)

usr
usr

Reputation: 171188

No that is the correct way to do it in your case. Your counts will probably always be 0 or 1 and be satisfied from an NC index.

In case you want to scan more data, it can be more efficient to do it like this:

select sum(case when user = 'x' then 1 end) UserCount, sum(case when email = 'x' then 1 end) EmailCount
from users

This will always scan the table. It depends on the data which version is faster. In your case, yours is faster.

Upvotes: 7

Related Questions