I work in the PHPMYADMIN environment. My goal was to download data from the Internet that describes judges, the regions in which they work, the files they made, the number of decisions, their salaries and similar things that I upload to my databases. 5 people worked on this project, creating 5 different databases, each of which symbolizes the specific region (part of the country) in which the judges work. Now my goal is, for example, to find a judge (working between the 5 databases) with the highest number of decisions, or with the highest salary for a particular year, the average judge’s salary for a particular year and similar things. The problem I came across is probably that I have the data uploaded in PHPMYADMIN, created 5 databases, where each represents one region, and I came across an error here, that using this display, I found the judge with the highest salary for 2018:
select id_sudca
, plat_2018
from sudy_sk.enrichment_platy
union all
select id_sudcu
, prijem_2018
from nabozny1.m_priznania
union all
select id_priznania
, prijmy_za_rok_2018
from putrovec.majetkove_priznania
order
by plat_2018;
And with this view, I found the average judge’s salary for 2018:
select AVG(plat_2018) FROM(select id_sudca, plat_2018 from sudy_sk.enrichment_platy union all select id_sudcu, prijem_2018 from nabozny1.m_priznania union all select id_priznania, prijmy_za_rok_2018 from putrovec.majetkove_priznania) tablealias;
And the point is that I think about a display that would find out what year (in the database I work with the years 2018-2015, of course I used the above-mentioned displays for these remaining years as 2017,2016,2015) was on average the best / worst for judges, simply that on average in which year they earned the most (all judges kicked) and in which at least.
I came up with a similar display (but to be honest, it looks awful, I don’t like it at all) and what is essentially not even right.
select AVG(platy) FROM (select id_sudca, plat_2018 from sudy_sk.enrichment_platy union all select id_sudcu, prijem_2018 from nabozny1.m_priznania union all select id_priznania, prijmy_za_rok_2018 from putrovec.majetkove_priznania order by plat_2018 UNION ALL
select id_sudca, plat_2017 from sudy_sk.enrichment_platy union all select id_sudcu, prijem_2017 from nabozny1.m_priznania union all select id_priznania, prijmy_za_rok_2017 from putrovec.majetkove_priznania order by plat_2017 UNION ALL
select id_sudca, plat_2016 from sudy_sk.enrichment_platy union all select id_sudcu, prijem_2016 from nabozny1.m_priznania union all select id_priznania, prijmy_za_rok_2016 from putrovec.majetkove_priznania order by plat_2016 UNION ALL
select id_sudca, plat_2015 from sudy_sk.enrichment_platy union all select id_sudcu, prijem_2015 from nabozny1.m_priznania union all select id_priznania, prijmy_za_rok_2015 from putrovec.majetkove_priznania order by plat_2015);
I work between 5 databases, but I only solve salary issues between 3 databases.
Therefore, I would like to advise or help with the correct display syntax.
2
Answers
You can add a literal value to each
union
member to indicate which year it corresponds to, then aggregate in the outer query:Then if you want to display the year with the greatest average, just add an
order by
clause to the query, and retain the top row only:I would strongly recommend normalizing your design. All this data should really be stored in a single table, with a column that stores the year.
Your structure is quite strange, you have columns for every year?
If you need the Average for every yoear you can add a column for the year anbd
GROUP BY
it