Using Union and Count(*) Together in SQL Query

Using union and count(*) together in SQL query

SELECT tem.name, COUNT(*) 
FROM (
  SELECT name FROM results
  UNION ALL
  SELECT name FROM archive_results
) AS tem
GROUP BY name
ORDER BY name

SQL Query to sum multiple count union queries

You don't need window functions for that

select sum(cnt) as total
from
(
  SELECT COUNT(col_x) as cnt FROM tablea
  UNION
  SELECT COUNT(col_y) FROM tableb
) tmp

How to do a count on a union query

If you want a total count for all records, then you would do this:

SELECT COUNT(*)
FROM
(
    select distinct profile_id 
    from userprofile_...

    union all

    select distinct profile_id 
    from productions_...
) x

How to find out the count from union query ?

You need to specify a name to column where COUNT calculation is being applied.

SELECT COUNT(*) FROM (
   SELECT Platform, DeviceCategory, COUNT(DISTINCT FullVisitorID) Count
   FROM [Laola].[AGG_0] 
   GROUP BY Platform, DeviceCategory
   UNION
   SELECT Platform, DeviceCategory, COUNT(DISTINCT FullVisitorID) 
   FROM [Laola].[AGG_2] 
   GROUP BY Platform, DeviceCategory
) x

SUM results of multiple COUNT (UNION) SQL query

WITH CTE AS (
SELECT Record_Table, COUNT(Record_Table) Records_In_Archive
FROM dbo.VIEW_GLOBAL_Search
WHERE Record_Table IN ('Maps','TB_Boxes')
GROUP BY Record_Table
ORDER BY Record_Table
)
SELECT ISNULL(Case Record_Table WHEN 'TB_Boxes' THEN 'Boxes' ELSE Record_Table 
END,'Total') Record_Table,
SUM(Records_In_Archive) Records_In_Archive
FROM CTE
GROUP BY Case Record_Table WHEN 'TB_Boxes' THEN 'Boxes' ELSE Record_Table END 
WITH ROLLUP
ORDER BY Record_Table

Although, if you are looking for a separate result:

SELECT 'Total', COUNT(Record_Table)
FROM dbo.VIEW_Global_Search
WHERE Record_Table IN ('TB_Boxes','Maps')

Why overthink this?

How to join two count queries together in one?

You could use a sub query that performs a union all, and then group and count:

select name, count(id) 
from (
        select reqname1 as name, entryid as id from table1
        union all
        select reqname2, comid from table2
     ) as combined
group by name

How to count two queries with UNION ALL

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.

---

Related Topics