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

Question

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 ;

barudo · Accepted Answer · 2016-11-13 09:57:24Z

1

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

edited Nov 13, 2016 at 9:57

answered Nov 13, 2016 at 9:51

barudo

6651 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

barudo Over a year ago

nope... only one record per person... with both "late counts" and "absent counts" for each record... there is a group by for the maintable...

ScaisEdge · Accepted Answer · 2016-11-13 09:16:52Z

0

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

edited Nov 13, 2016 at 9:16

answered Nov 13, 2016 at 9:15

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

But this will eliminate names which were either only absent or only late.

ScaisEdge Over a year ago

@TimBiegeleisen thaks correte update the left join .. and eventully the OP could use a table for all the name and left join the table late and absentt for avoid missing name

navintb · Accepted Answer · 2016-11-13 09:36:28Z

0

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.

answered Nov 13, 2016 at 9:36

navintb

1295 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

I haven't tried ... Had you tried you would have discovered that there is no full join in MySQL.

mester Over a year ago

Thanks it had worked,but without full join I only used join .

Tim Biegeleisen · Accepted Answer · 2016-11-13 09:51:30Z

0

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

edited Nov 13, 2016 at 9:51

answered Nov 13, 2016 at 9:21

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

J-Mean · Accepted Answer · 2016-11-13 10:13:57Z

0

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

edited Nov 13, 2016 at 10:13

answered Nov 13, 2016 at 9:46

J-Mean

1,2101 gold badge9 silver badges15 bronze badges

4 Comments

Tim Biegeleisen Over a year ago

Again, your query will filter off people who were only absent, but not late.

J-Mean Over a year ago

Now this will return both tables records!! Please let me know your view. Thanks!!

Tim Biegeleisen Over a year ago

There is no full outer join in MySQL. But maybe the OP does not care about this edge case.

J-Mean Over a year ago

I have to thank you because I learn something new from your query. Thanks!!

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

2 Comments

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related