Combine multiple table and use Group By Function in MYSQL

Question

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.

select number,count() as total from tb01 group by number union all select number,count() as total from tb02 group by number... then how should I again group by? — Pathi
– Pathi, Commented Oct 13, 2022 at 9:46
I don't get the logic here - is it guaranteed that the first five number in every table is the same number if not then what? — P.Salmon
– P.Salmon, Commented Oct 13, 2022 at 10:06

O. Jones · Accepted Answer · 2022-10-13 10:18:58Z

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.

Collectives™ on Stack Overflow

Combine multiple table and use Group By Function in MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related