CODEWITHSUNDEEP
mysqlcount
mootymoots
mootymoots

Reputation: 4575

Overall Unique count from two tables in MySQL

I have two tables, both having column a device_id column that I want to count. For the purposes of demonstration, the schema looks like:

Table 1: 'id', 'save_val', 'device_id_major'
Table 2: 'id', 'save_val', 'location', 'device_id_team'

Table 1 could have many of the same 'device_id_major'.

I basically want to get the unique device_id's from both tables, then from that result set, get the count of unique device_id's (the same device_id can appear in both tables).

Is this possible in one query?

Upvotes: 0

Views: 634

Answers (3)

mootymoots
mootymoots

Reputation: 4575

SELECT count(DISTINCT aa.id) 
FROM (SELECT DISTINCT major_id AS id FROM `major` 
UNION ALL
SELECT DISTINCT team_id AS id FROM `team`) 
AS aa

This seems to do the trick.

Upvotes: 1

Jeff Watkins
Jeff Watkins

Reputation: 6357

select distinct aa.device_id, count(*) 
from(select distinct device_id from table1
union all
select distinct device_id from table2) as aa
group by device_id
order by device_id

Or something like... As I don't have the schema to hand, I can't fully validate it.

Upvotes: 1

Anthony Atkinson
Anthony Atkinson

Reputation: 3248

You could use a query that takes the UNION of both tables, then SELECT the unique values.

Upvotes: 0

Related Questions