Trying to get count of two tables to show in SQL Query

Question

I'm trying to get the count from two different tables and group them together. The problem I am running into is that I either get really long query times 30 mins + or it just doesn't display the way I want it to.

I got the code for this part to semi work and running fast using a union. (Which is most likely wrong, but all my attempts at trying with a join have made the run times incredibly long)

select `model`, count(*) as "Test1"
from `table1`
group by `model`

union

select `model`, count(*) as "Test2"
from `table2`
group by `model`

This is getting to show the counts on a huge scrolling list, where the first count is being displayed then the other. Also while only displaying the "Test1" text, the "Test2" wont display.

I would like to have it so that they display next to each other so that I can see how many of one model I have compared to the other tables model.

Any help would be appreciated, sorry for any confusion first time posting here

EDIT: Tried my best to give a small sample of what I'm seeing with the union code

    Model |Test1
    Suzuki|  2
    Honda |  19
    Suzuki|  5
    Honda |  26

so something like that would show up, where the first 2 results would be from the first select statement and the other 2 would be from the second select statement.

The result I'm looking for is more like this.

 Model | Test1    Model | Test2
 Suzuki|  2      Suzuki|  5
 Honda |  19     Honda |  26

Can you show some data sample? And how it should be arranged! — Jorge Campos, Nov 30 '15 at 02:19
Are the models in the two tables the same or they can be different? — Jorge Campos, Nov 30 '15 at 04:26
@JorgeCampos The models are the same for both tables, one table has all possible models, the other possibly only has some of them. (I can try to explain it better if you need me to.) — Fumbles, Nov 30 '15 at 04:32

Turophile · Accepted Answer · 2015-11-30T06:14:59.163

This is difficult to do in mySQL because it doesn't support FULL OUTER JOIN like other RDBMS.

I think you can get what you want with this:

select * from
(
select t1.model, count(*) as "Test1"
from table1 as t1
group by model
) as tt1
left outer join 
(
select t2.model, count(*) as "Test2"
from table2 as t2
group by model
) as tt2
ON tt1.model = tt2.model 
UNION
select * from
(
select t3.model, count(*) as "Test1"
from table1 as t3
group by model
) as tt3
right outer join 
(
select t4.model, count(*) as "Test2"
from table2 as t4
group by model
) as tt4
ON tt3.model = tt4.model

Here it is working in a fiddle:http://sqlfiddle.com/#!9/5a155/1

However, most people would settle for a different output format which does not require so much gymnastics.

EDIT Due to an answer to one of the comments.

If you know that table2 doesn't contain any model not in table1, then the SQL becomes simpler:

select model, 
(select count(*) as "Test1"
from table1 as t1
where t0.model = t1.model 
) as "Test1",
(select count(*) as "Test1"
from table2 as t2
where t0.model = t2.model
) as "Test2"
from table1 as t0
group by model 
;

Fiddle is here: http://sqlfiddle.com/#!9/5a155/33

Wow thank you so much, finally works! Been trying for hours. I was originally trying to do it as a inner join, but I was getting insane query run times and trying out union it was just so much faster so that's why I tried to make it work — Fumbles, Nov 30 '15 at 04:49
Glad I could help. Here's a Q&A about simulating FULL OUTER JOIN in mySQL: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Turophile, Nov 30 '15 at 05:58

Trying to get count of two tables to show in SQL Query

1 Answers1