mester mester - 26 days ago 6
MySQL Question

How to fetch data from two tables in MySQL and count each one?

How can I write this in one query ?

I have two tables one likes this called (late):

id name S_id
1 A 6
2 A 6
3 B 5
4 C 8
5 A 6
6 A 6
7 C 8
8 C 8


The other one likes this called (absent):

id name S_id
1 A 6
2 A 6
3 A 6
4 A 6
5 A 6
6 A 6
7 B 5
8 c 8


I want the results like this table:

where (count late) counts times of late and (count absent) counts time of absents.

name Count late Count absent
A 4 6
B 1 1
C 3 1


I tried something like this:

this didn't work !

SELECT
*
FROM
(SELECT name, COUNT(*) AS '# count absent' FROM absent GROUP BY s_id) t1
INNER JOIN
(SELECT name, COUNT(*) AS '# count Late' FROM late GROUP BY s_id) t2
ON t1.s_id = t2.s_id ;

Answer

Use Union of the two tables: lates and absents... then sum up the number of lates and absents.

Try this:

SELECT 
    SUM(tardies) as 'total_lates', SUM(absences) as 'total_absences', name, s_id
FROM
    ((SELECT
        COUNT(*) as 'tardies',
        0 as 'absences',
        name,
        s_id
    FROM 
        lates
    GROUP BY
        s_id
    )
UNION
    (SELECT 
        0 as 'tardies',
        COUNT(*) as 'absences',
        name,
        s_id
     FROM
        absents
     GROUP BY
        s_id
    )
)
as maintable
GROUP by s_id
ORDER BY name