CODEWITHSUNDEEP
mysql
mester
mester

Reputation: 25

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 ;

Upvotes: 2

Views: 63

Answers (5)

J-Mean
J-Mean

Reputation: 1200

Please try following. It worked for me. 

select 
   coalesce( t1.name,t2.name) name,coalesce( t1.late,0) ,coalesce(t2.[absent],0) 
   from 
      (select name,s_id, count(*) as 'late' from late group by s_id,name ) t1 
   FULL OUTER JOIN
      (select name,s_id, count(*) as 'absent' from [absent] group by s_id,name ) 
   t2 on t1.s_id = t2.s_id 
   order by name

Upvotes: 0

barudo
barudo

Reputation: 665

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

You need a full outer join here to make sure that you retain people who were either late or absent, but not both. Well, MySQL has no built in full outer join support, but it can be simulated:

SELECT t1.name,
       t2.late_cnt,
       t1.absent_cnt
FROM
(SELECT s_id, name, COUNT(*) AS absent_cnt
  FROM absent GROUP BY s_id, name) t1
LEFT JOIN
(SELECT s_id, name, COUNT(*) AS late_cnt
  FROM late GROUP BY s_id, name) t2
    ON t1.s_id = t2.s_id
UNION
SELECT t1.name,
       t2.late_cnt,
       t1.absent_cnt
FROM 
(SELECT s_id, name, COUNT(*) AS absent_cnt
 FROM absent GROUP BY s_id, name) t1
RIGHT JOIN
(SELECT s_id, name, COUNT(*) AS late_cnt
 FROM late GROUP BY s_id, name) t2
    ON t1.s_id = t2.s_id

Upvotes: 0

navintb
navintb

Reputation: 129

SELECT 
  (case when t1.name is not null then t1.name else t2.name end) as name,t1.absent,t2.late
  FROM
  (SELECT name, COUNT(*) AS 'absent' 
      FROM absent GROUP BY name)  t1 
  FULL JOIN
  (SELECT  name, COUNT(*) AS 'late' 
      FROM late   GROUP BY name)  t2
  ON t1.name = t2.name ;

Try this. I haven't tried. Hope it works.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

If you need join on s_id you must select these column in subselect 

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

otherwise the resulting select can't be join because there aren't the columns for this

Upvotes: 0

Related Questions