Kavya Shree Kavya Shree - 10 months ago 57
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

Similaly in table 2:training_details

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


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 Source
SELECT advisor, COUNT(*) as TheCount
     SELECT advisor FROM table 1
     SELECT advisor FROM table 2
     ) AS [TheNames]
GROUP BY advisor


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