Reputation: 23
how to execute a "MySQL" query for all values that are in my column ?
Here is my table
Table A
|------------------|
| horse |
|------------------|
| C.Ferland |
| Abrivard |
| P. Hawas |
|------------------|
Table B
|----------------------|---------------------|------------------|
| id | CL | horse |
|----------------------|---------------------|------------------|
| 1 | 1er | C.Ferland |
| 2 | 5e | Abrivard |
| 3 | 3e | P. Hawas |
| 4 | 7e | Rafin |
| 5 | 3e | edouard |
| 6 | 6e | Loui |
| 8 | 12e | Elsa |
| 9 | 8e | Jseph |
|----------------------|---------------------|------------------|
I want the output to be:
+------------+--------+---------+---------+-----------+
| horse | Top_1 | Top_2_3 | TOP_4_5 | TOP_total |
+------------+--------+---------+---------+-----------+
| C. Ferland | 0.1757 | 0.2788 | 0.1892 | 0.6436 |
| Abrivard | 0.0394 | 0.1231 | 0.1575 | 0.3199 |
| P. Hawas | 0.0461 | 0.1263 | 0.1092 | 0.2816 |
+------------+--------+---------+---------+-----------+
Currently, I'm using this query for a table and it's very good.
select
horse
sum(cl = 1) / count(*) over() top_1,
sum(cl in (2,3)) / count(*) over() top_2_3,
sum(cl in (4,5)) / count(*) over() top_4_5,
sum(cl <= 5) / count(*) over() top_1_5
from p_mu.jour
group by horse
How to join the column table A with the CL of my table B
Upvotes: 0
Views: 54
Reputation: 164234
There is no need for a join.
Use the operator IN:
select t.*
from (
select
horse,
sum(cl = 1) / count(*) over() top_1,
sum(cl in (2,3)) / count(*) over() top_2_3,
sum(cl in (4,5)) / count(*) over() top_4_5,
sum(cl <= 5) / count(*) over() top_1_5
from p_mu.jour
group by horse
) t
where t.horse in (select horse from TableA)
or EXISTS:
select t.*
from (
select
horse,
sum(cl = 1) / count(*) over() top_1,
sum(cl in (2,3)) / count(*) over() top_2_3,
sum(cl in (4,5)) / count(*) over() top_4_5,
sum(cl <= 5) / count(*) over() top_1_5
from p_mu.jour
group by horse
) t
where exists (select 1 from TableA where horse = t.horse)
Upvotes: 1