Single Query to Count Data from Multiple Table Mysql

Question

I have tables that contain same field, for example:

p_central_ticket        p_south_ticket             p_west_ticket 
=====================================================================
 - t_id                  - t_id                     - t_id
 - t_req_type            - t_req_type               - t_req_type  
 - t_status              - t_status                 - t_status

And i have one table :

m_event_type
=============
- ev_type
- ev_activity

My current query :

SELECT ev_activity AS Activity, COUNT( * ) AS Total
  FROM m_event_type
LEFT JOIN p_central_ticket ON p_central_ticket.t_req_type = m_event_type.ev_type
  WHERE t_status =9
GROUP BY ev_activity

Output from query above:

My question is, how should i do, if i want to total count from 3 tables above.

(For Example Activity Change Request total 18000, count from p_central_ticket + p_south_ticket + p_west_ticket) etc.

Thanks...

I think I would have started with ONE table for tickers which contained a column identifying that they were central, south or west — RiggsFolly
– RiggsFolly, Commented Feb 14, 2019 at 9:14

JohnHC · Accepted Answer · 2019-02-14 09:14:12Z

1

Use union all in a subquery, then join it:

select t1.ev_Activity, count(t1.*) as total
from m_event_type t1
LEFT JOIN 
    (
    select *
    from p_central_ticket 
    WHERE t_status =9
    union all
    select *
    from p_south_ticket 
    WHERE t_status =9
    union all
    select *
    from p_west_ticket 
    WHERE t_status =9
    ) t2
ON t2.t_req_type = t1.ev_type
GROUP BY t1.ev_activity

answered Feb 14, 2019 at 9:14

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

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

Comments

Eponyme Web · Accepted Answer · 2019-02-14 09:22:09Z

1

Use a UNION ALL (to avoid removing duplicates) then SUM the quantities Note that it also works if your tables have different column names you only need to alias them in the select.

SELECT ev_activity AS Activity, SUM(quantity) AS Total
FROM m_event_type met
LEFT JOIN (SELECT c.t_req_type, COUNT(*) as quantity
       FROM p_central_ticket c
       WHERE c.t_status =9
       GROUP BY c.t_req_type
       UNION ALL
       SELECT s.t_req_type, COUNT(*)
       FROM p_south_ticket s
       WHERE s.t_status =9
       GROUP BY s.t_req_type
       UNION ALL
       SELECT w.t_req_type, COUNT(*)
       FROM p_west_ticket w
       WHERE w.t_status =9
       GROUP BY w.t_req_type) p ON
p.t_req_type = met.ev_type
GROUP BY ev_activity

answered Feb 14, 2019 at 9:22

Eponyme Web

9765 silver badges10 bronze badges

2 Comments

Lutfi Aldi Over a year ago

It also works man, but stackoverflow just allow me to click accept answer just once. I just mark your answer to be useful answer, sorry man @Eponyme Web

Eponyme Web Over a year ago

@aldi thanks, I appreciate. Di you notice any performance difference between the various working queries ?

ScaisEdge · Accepted Answer · 2019-02-14 09:14:57Z

0

You could use UNION ALL for select all the rows of the 3 tables

        SELECT ev_activity AS Activity, COUNT( * ) AS Total
          FROM m_event_type
        LEFT JOIN (

        select t_id, t_req_type, t_status
        from p_central_ticket 
        union all 
        select t_id, t_req_type, t_status
        from p_south_ticket
        union all 
        select t_id, t_req_type, t_status
        from p_west_ticket
        ) t ON t.t_req_type = m_event_type.ev_type
          WHERE t.t_status =9
        GROUP BY ev_activity

answered Feb 14, 2019 at 9:14

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Single Query to Count Data from Multiple Table Mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related