Combine multiple table and use Group By Function in MYSQL-CodePudding

I have 5 different datasets from 5 different tables.. From those 5 different tables I have taken below group by data..

select number,count(*) as total from tb01 group by number limit 5;
select number,count(*) as total from tb02 group by number limit 5;

Like that I can retrieve 5 different datasets. Here is an example.

 ----------- ------- 
| number    | total |
 ----------- ------- 
| 114000259 |     1 |
| 114000400 |     1 |
| 114000686 |     1 |
| 114000858 |     1 |
| 114003895 |     1 |
 ----------- -------

Now I need to combine those 5 different tables such as below tabular format.

 ----------- ------- ------- ------- 
| number    | tb01  | tb02  | tb03  |
 ----------- ------- ------- ------- 
| 114000259 |     1 |     2 |     1 |
| 114000400 |     1 |     0 |     1 |
| 114000686 |     1 |     3 |     1 |
| 114000858 |     1 |     1 |     5 |
| 114003895 |     1 |     0 |     1 |
 ----------- ------- ------- -------

Can someone help me to combine those 5 grouped data sets and get the union as above.

Note: I dont need the header as same as table names..these headers can be anything Further I dont need to limit 5, above is to get a sample of 5 data only. I have a large dataset.

CodePudding user response：

It's a job for JOINs and subqueries. My answer will consider three tables. It should be obvious how to expand it to five.

Your first subquery: get all possible numbers.

            SELECT number FROM tb01 UNION
            SELECT number FROM tb02 UNION
            SELECT number FROM tb03

Then you have a subquery for each table to get the count.

          SELECT number, COUNT(*) AS total
            FROM tb02 GROUP BY number

Then you LEFT JOIN everything and SELECT from that.

SELECT numbers.number, 
       tb01.total tb01, 
       tb02.total tb02,
       tb03.total tb03
  FROM (
            SELECT number FROM tb01 UNION
            SELECT number FROM tb02 UNION
            SELECT number FROM tb03
        ) numbers
  LEFT JOIN (
          SELECT number, COUNT(*) AS total
            FROM tb01 GROUP BY number
        ) tb01 ON numbers.number = tb01.number
  LEFT JOIN (
          SELECT number, COUNT(*) AS total
            FROM tb02 GROUP BY number
        ) tb02 ON numbers.number = tb02.number
  LEFT JOIN (
          SELECT number, COUNT(*) AS total
            FROM tb03 GROUP BY number
        ) tb03 ON numbers.number = tb01.number

You can add ORDER BY and LIMIT clauses to that overall query as necessary.

The first subquery together with the LEFT JOIN ensures that you get results even if some of your tables are missing number rows. (Some DBMSs have FULL OUTER JOIN, but MySQL does not.)

Pro tip: If you use LIMIT without ORDER BY, you get an unpredictable subset of your rows. Unpredictable is worse than random, because you get the same subset in testing with small tables, but when your tables grow you may start getting different subsets. You'll never catch the problem in unit testing. LIMIT without ORDER BY is a serious error.