Mysql Query select count(*) from multiple tables

Question

I have three tables movie_request,movie_request_2 and movie_request_3 I want to get count() from each table and return as one row, with each table count() as a column.

movie_request is free_cnt, movie_request_2 is plus_cnt, movie_request_3 is visitor_cnt

I expect results like

movie_id | movie_title | free_cnt | plus_cnt | visitor_cnt
   10    |   Test      |    5     |    10    |    15
   11    |   Test2     |    6     |    11    |    25

Here is my query

SELECT movie_title,movie_id,MAX(x.free_cnt) as free_cnt, MAX(x.plus_cnt) as plus_cnt, MAX(x.visitor_cnt) as visitor_cnt, (MAX(x.free_cnt) + MAX(x.plus_cnt) + MAX(x.visitor_cnt)) AS total
        FROM (
            SELECT a1.id as movie_id, a1.movie_title as movie_title, 0 as plus_cnt, 0 as visitor_cnt, COUNT(*) AS free_cnt
            FROM movie_request a1
            GROUP BY a1.movie_title
            UNION ALL

            SELECT a2.id as movie_id, a2.movie_title as movie_title,  0 as free_cnt, 0 as visitor_cnt, COUNT(*) AS plus_cnt
            FROM movie_request_2 a2
            GROUP BY a2.movie_title
            UNION ALL

            SELECT a3.id as movie_id, a3.movie_title as movie_title,  0 as plus_cnt, 0 as free_cnt,  COUNT(*) AS visitor_cnt
            FROM movie_request_3 a3
            GROUP BY a3.movie_title
        ) AS x
        GROUP BY movie_title

I keep getting results like this

 movie_id | movie_title | free_cnt | plus_cnt | visitor_cnt
       10    |   Test      |    5     |    0    |    0
       11    |   Test2     |    6     |    0    |    0

I've tested results separetely and they all return results.

How do i solve?

I think order of columns in the inner queries might be affecting it. — pkgajulapalli, Jun 08 '18 at 11:10
Is there a `movie` table? Why is movie title repeated in each table??? — Bohemian, Jun 08 '18 at 11:12

score 1 · Accepted Answer · answered Jun 08 '18 at 11:10

Within the various selects, keep the same column order.
So

SELECT   a1.id as movie_id,
         a1.movie_title as movie_title,
         COUNT(*) AS free_cnt
         0 as plus_cnt,
         0 as visitor_cnt
FROM     movie_request a1
GROUP BY a1.movie_title

UNION ALL

SELECT   a2.id as movie_id,
         a2.movie_title as movie_title,
         0 as free_cnt,
         COUNT(*) AS plus_cnt,
         0 as visitor_cnt
FROM     movie_request_2 a2
GROUP BY a2.movie_title

UNION ALL

SELECT   a3.id as movie_id,
         a3.movie_title as movie_title,
         0 as plus_cnt,
         0 as free_cnt,
         COUNT(*) AS visitor_cnt
FROM     movie_request_3 a3
GROUP BY a3.movie_title

score 1 · Answer 2 · answered Jun 08 '18 at 11:13

1

I got a solution by referring the below link

https://stackoverflow.com/questions/3761240/count-from-multiple-tables-in-mysql

answered Jun 08 '18 at 11:13

Pavithra

21
7

score 0 · Answer 3 · answered Jun 08 '18 at 11:09

One option would be to union together the three tables along with a computed column. Then, use conditional aggregation and pivot out the counts you want:

SELECT
    t.movie_id,
    t.movie_title,
    COUNT(CASE WHEN t.label = 'free'    THEN 1 END) AS free_cnt,
    COUNT(CASE WHEN t.label = 'plus'.   THEN 1 END) AS plus_cnt,
    COUNT(CASE WHEN t.label = 'visitor' THEN 1 END) AS visitor_cnt
FROM
(
    SELECT movie_id, movie_title, 'free' AS label
    FROM movie_request
    UNION ALL
    SELECT movie_id, movie_title, 'plus'
    FROM movie_request_2
    UNION ALL
    SELECT movie_id, movie_title, 'visitor'
    FROM movie_request_3
) t
GROUP BY
    t.movie_id, t.movie_title;

By the way, your schema design really leaves something to be desired here. You should not normally have to write such ugly queries for simple reporting problems. Most likely, you should just have a single movie request table, possibly with a computed column as I had to generate above.

Mysql Query select count(*) from multiple tables

3 Answers3