Kavya Shree Kavya Shree - 24 days ago 9
MySQL Question

SQL Query - Get distinct count of two tables

Table 1(student_details)

Is having 532 datas with different advisor each data is assigned to one advisor like

id advisor
-- -------
1 Bala
2 Shanmugam
3 Priya
4 Bala
etc..


Similaly in table 2:training_details

id advisor
-- -------
1 Bala

etc..


I tried this query this will return count of people assigned to each advisor alone from table1.I just want to count from two tables and select advisor

SELECT DISTINCT t1.advisor as advisor,IFNULL(COUNT(t2.advisor), 0) AS total FROM advisor AS t1
LEFT JOIN student_details AS t2 ON t1.advisor = t2.advisor AND t2.del!=1 GROUP BY t1.advisor;


Similarly I Just want to join two table entry and return distinct advisor with equal count in 2columns;

Answer
SELECT advisor, COUNT(*) as TheCount
FROM
    (
     SELECT advisor FROM table 1
     UNION ALL
     SELECT advisor FROM table 2
     ) AS [TheNames]
GROUP BY advisor

or

SELECT x.advisor, COUNT(x.advisor)
    FROM (SELECT advisor
            FROM table1
          UNION ALL
          SELECT advisor
            FROM table2
         ) x
GROUP BY x.advisor