Sohrab Delsohrab
Sohrab Delsohrab

Reputation: 23

MySQL Joins table with SUM

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

Answers (1)

forpas
forpas

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

Related Questions