Selecting multiple COUNTs from same table

Databases
1

I have two queries I need to run:

SELECT COUNT(user_id) AS count_u FROM users WHERE username LIKE '$user'

SELECT COUNT(user_id) AS count_e FROM user WHERE user_email LIKE '$email'

Here were a couple of my failed attempts at trying to merge the queries:

SELECT COUNT(u1.user_id) AS count_u FROM users u1 WHERE
u1.username LIKE '$user' UNION SELECT COUNT(u2.user_id) AS count_e
FROM users u2 WHERE u2.user_email LIKE '$email'

SELECT COUNT(u1.user_id) AS count_u, COUNT(u2.user_id) AS count_e
FROM users u1, users u2 WHERE u1.username LIKE '$user'
AND u2.user_email LIKE '$email'

The first one gave me only the count_u column, but the rows showed accurate results. The second one gave me both columns, but they showed inaccurate results. I had thought about using JOINs, but the two queries seem totally distinct…there’s no common element. Know of any way to do this? Or should I just execute them separately?

2

the first one is almost correct:

SELECT COUNT(u1.user_id) AS count
     , 'count_u' as type
  FROM users u1
 WHERE u1.username LIKE '$user'
 UNION ALL
SELECT COUNT(u2.user_id) AS count
     , 'count_e'
  FROM users u2
 WHERE u2.user_email
  LIKE '$email'
3

There isn’t any way to separate the counts as separate columns, though, is there?

4

sure there is

select ( SELECT COUNT(u1.user_id)
           FROM users u1
          WHERE u1.username LIKE '$user' ) as count_u
     , ( SELECT COUNT(u2.user_id)
           FROM users u2
          WHERE u2.user_email LIKE '$email' ) as count_e

alternatively,

select count(case when username like '$user'
                  then 937 ) as count_u
     , count(case when user_email like '$email'
                  then 937 ) as count_e
  from users
5

That worked great! I used the first one that r937 proposed, with the subqueries. I also removed the table aliases, since they weren’t needed anymore.

6