How to join results from two tables in one output file?

Question

I have two tables in MySQL DB; table1, table2.

Both of them have a single column (float) values. It's actually a dump from our research project which produces a single value result.

And many of these values get repeated and sorting and filtering them in Python would be cumbersome, so I though perhaps dumping them in a table in DB would be quicker.

So the end result from the SQL query is the following grouped by the value:

value    table1_count   table2_count
1.0          0               1
1.1          1               3
2.1          4               5

The query I am coming up with is the following:

select everything.value, everything.count1, everything.count2
from
((
select X as value, count(*)  from table1 
) union all (
select X as value, count (*) from table2 
)) everything
group by everything.value
into outfile "/count";

Any suggestions?

Thanks,

Answer 1

You can't do counts by group in the inner queries, since you're defining the groups in the outer query. This should be simpler:

select everything.value, count(*)
from
(
  select X as value from table1
    union all 
  select X from table2
) everything
group by value
into outfile "/count";

Also here's some trivia: when you use UNION, you need to define column aliases only in the first query unioned.

---

Re your comment. Here's one solution:

select everything.value, sum(t = 'success') as s, sum(t = 'failure') as f
from
(
  select X as value, 'success' as t from table1
    union all 
  select X, 'failure' from table2
) everything
group by value
into outfile "/count";

This uses a trick in MySQL that boolean expressions return 0 for false or 1 for true. So when you sum up a bunch of expressions, you get a count of the rows where the expression is true. (Don't rely on this trick in other brands of SQL database.)