How to count two queries with UNION ALL

Question

I have this query:

    SELECT ava_users.*, 0 AS ord
    FROM ava_friend_requests
    LEFT JOIN ava_users 
    ON ava_friend_requests.from_user = ava_users.id
    WHERE ava_friend_requests.to_user = $user[id]
    UNION ALL
    SELECT ava_users.*, 1 AS ord
    FROM ava_friends
    LEFT JOIN ava_users 
    ON ava_friends.user2 = ava_users.id
    WHERE ava_friends.user1 = $user[id]
    ORDER BY ord
    LIMIT $from, $display_num

As you can see there are two queries with a UNION ALL.

Now my question is: How do I count EACH query? How can I retrieve each count value? I'd like to have a count result for query no 1 and a count result for query no2.

UPDATE: I would like to have a counting result like this: count 1: 34, count 2: 45

See here: select count from multiple tables

What are you trying to count? Can you provide sample output? — Gordon Linoff, Aug 11 '13 at 15:49
First of all you need to add count to your query + as @Gordon Linoff said; can you provide some sample data with sample output. here is an example `select * , count(1) as ava_friend_request_count, 0 ava_friends_count union all select * , 0 as ava_friend_request_count, count(1) ava_friends_count` — Nilesh, Aug 11 '13 at 15:53
@Gordon Linoff: Query1 selects the requests and query2 selects the friends. I need to know the number of each query (requests and friends). — drpelz, Aug 11 '13 at 16:29
@drpelz . . . Do you need to know it before or after the `limit` gets applied? — Gordon Linoff, Aug 11 '13 at 16:48
@Gordon Linoff: After my limit. Would be great if I could save each count result into an appropriate variable so I know how many number of requests or friends I have respectively. — drpelz, Aug 11 '13 at 16:52

Gordon Linoff · Accepted Answer · 2013-08-11T16:54:48.943

If you want to count the returned rows from the two subqueries, how about this:

SELECT 'Query1' as which, count(*) as cnt
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]
UNION ALL
SELECT 'Query2', count(*)
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id];

EDIT:

To do this after the limit, make the current query a subquery and do the counting:

select ord, count(*) as cnt
from (SELECT ava_users.*, 0 AS ord
      FROM ava_friend_requests
      LEFT JOIN ava_users 
      ON ava_friend_requests.from_user = ava_users.id
      WHERE ava_friend_requests.to_user = $user[id]
      UNION ALL
      SELECT ava_users.*, 1 AS ord
      FROM ava_friends
      LEFT JOIN ava_users 
      ON ava_friends.user2 = ava_users.id
      WHERE ava_friends.user1 = $user[id]
      ORDER BY ord
      LIMIT $from, $display_num
     ) t
group by ord;

It also might be easier just to count the ord column at the application level.

@drpelz . . . This is a separate query that returns the counts. You still have to run your original query to get the data. — Gordon Linoff, Aug 11 '13 at 17:11

score 1 · Answer 2 · edited Aug 11 '13 at 16:19

You may want to try this:

select count(*) from (SELECT ava_users.*, 0 AS ord
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]
UNION ALL
SELECT ava_users.*, 1 AS ord
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id]
ORDER BY ord
LIMIT $from, $display_num) as users;

This means to deal with you union all like a table and count all the elements.

How to count two queries with UNION ALL

2 Answers2