how to execute a "MySQL" query for all values that are in my column ?
Here is my table
Table A
|----------------------|---------------------|------------------|
| id | CL | horse |
|----------------------|---------------------|------------------|
| 1 | 1er | C.Ferland |
| 2 | 5e | Abrivard |
| 3 | 3e | P. Hawas |
|----------------------|---------------------|------------------|
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 running this query for a value in my horse column. And that works very well.
SELECT horse,
sum(case when `cl` = '1er' then 1 else 0 end)/count(*) as Top_1,
sum(case when `cl` BETWEEN 2 AND 3 then 1 else 0 end)/count(*) as Top_2_3,
sum(case when `cl` BETWEEN 4 AND 5 then 1 else 0 end)/count(*) as TOP_4_5,
sum(case when `cl` BETWEEN 1 AND 5 then 1 else 0 end)/count(*) as TOP_total
FROM p_mu.cachedate
WHERE horse ="C.Ferland";
How to adapt this query for all the values in my "horse" column. Thank you for your help..
You can use conditional aggregation, but your desired results show that you want to compute the average over the whole dataset, not only over the rows of the group. Window functions come handy for this:
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.cachedate
group by horse
If you want to filter on a given horse, you need a derived table:
select *
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.cachedate
group by horse
) t
where horse = 'C.Ferland'
This works in MySQL 8.0 only. In earlier versions, you can use a subquery instead:
select
horse,
sum(cl = 1) / x.cnt top_1,
sum(cl in (2,3)) / x.cnt top_2_3,
sum(cl in (4,5)) / x.cnt top_4_5,
sum(cl <= 5) / x.cnt top_1_5
from p_mu.cachedate
inner join (select count(*) cnt from p_mu.cachedate) x
group by horse