MySQL Joins table with SUM

Question

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

It's best to sync table and names published with query. Where for example is jockey? — P.Salmon
– P.Salmon, Commented Jun 22, 2020 at 15:11

forpas · Accepted Answer · 2020-06-22 19:27:45Z

1

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)

edited Jun 22, 2020 at 19:27

answered Jun 22, 2020 at 17:47

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Sohrab Delsohrab Over a year ago

I don't understand the Select 't. *' .. and ) t .. and select 1?

forpas Over a year ago

t is the alias of your query which in my code is a subquery. select t.*... means select all the columns of that subquery aliased t. The where clause in the end will filter the results of your query so only horses that exist in TableA will be returned. The 1 in select 1 is just a column placeholder. It could be select 0 or select null, it doesn't matter. What is important here is that there exists a row in TableA with the same horse as in t.

Sohrab Delsohrab Over a year ago

I must necessarily make an error in the reproduction of the query I have an error message

Sohrab Delsohrab Over a year ago

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sum(cl = 1) / count() over() top_1, sum(cl in (2,3)) / count() over()' at line 5

Sohrab Delsohrab Over a year ago

you just saved me 3 to 4 days of research ... Thank you

|

Collectives™ on Stack Overflow

MySQL Joins table with SUM

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related