skip to Main Content

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


  1. You can add a literal value to each union member to indicate which year it corresponds to, then aggregate in the outer query:

    select yr, avg(plat) as avg_plat
    from (
        select 2018 as yr, id_sudca, plat_2018 as plat from sudy_sk.enrichment_platy 
        union all select 2018, id_sudcu, prijem_2018 from nabozny1.m_priznania 
        union all select 2018, id_priznania, prijmy_za_rok_2018 from putrovec.majetkove_priznania 
        union all select 2017, id_sudca, plat_2017 from sudy_sk.enrichment_platy 
        union all select 2017, id_sudcu, prijem_2017 from nabozny1.m_priznania 
        union all select 2017, id_priznania, prijmy_za_rok_2017 from putrovec.majetkove_priznania 
        union all select 2016, id_sudca, plat_2016 from sudy_sk.enrichment_platy 
        union all select 2016, id_sudcu, prijem_2016 from nabozny1.m_priznania 
        union all select 2016, id_priznania, prijmy_za_rok_2016 from putrovec.majetkove_priznania 
        union all select 2015, id_sudca, plat_2015 from sudy_sk.enrichment_platy 
        union all select 2015, id_sudcu, prijem_2015 from nabozny1.m_priznania 
        union all select 2015, id_priznania, prijmy_za_rok_2015 from putrovec.majetkove_priznania
    ) t
    group by yr
    

    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:

    order by avg_plat desc limit 1
    

    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.

    Login or Signup to reply.
  2. Your structure is quite strange, you have columns for every year?

    select AVG(platy) 
    FROM 
        (select 2018 yyear,id_sudca, plat_2018 as platy from sudy_sk.enrichment_platy) 
        union all (select 2018 yyear, id_sudcu, prijem_2018 from nabozny1.m_priznania) 
        union all (select 2018 yyear,id_priznania, prijmy_za_rok_2018 from putrovec.majetkove_priznania order by plat_2018 )
        UNION ALL (select 2017 yyear,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
        );
    

    If you need the Average for every yoear you can add a column for the year anbd GROUP BY it

    select yyear,AVG(platy) 
    FROM 
        (   
            (select 2018 yyear,id_sudca, plat_2018 as platy from sudy_sk.enrichment_platy) 
            union all (select 2018 yyear, id_sudcu, prijem_2018 from nabozny1.m_priznania) 
            union all (select 2018 yyear,id_priznania, prijmy_za_rok_2018 from putrovec.majetkove_priznania order by plat_2018 )
            UNION ALL (select 2017 yyear,id_sudca, plat_2017 from sudy_sk.enrichment_platy )
            union all (select 2017 yyear,id_sudcu, prijem_2017 from nabozny1.m_priznania )
            union all (select 2017 yyear,id_priznania, prijmy_za_rok_2017 from putrovec.majetkove_priznania order by plat_2017 )
            UNION ALL (select 2016 yyear,id_sudca, plat_2016 from sudy_sk.enrichment_platy) 
            union all (select 2016 yyear,id_sudcu, prijem_2016 from nabozny1.m_priznania) 
            union all (select 2016 yyear,id_priznania, prijmy_za_rok_2016 from putrovec.majetkove_priznania order by plat_2016) 
            UNION ALL (select 2015 yyear,id_sudca, plat_2015 from sudy_sk.enrichment_platy)
            union all (select 2015 yyear,id_sudcu, prijem_2015 from nabozny1.m_priznania )
            union all (select 2015 yyear,id_priznania, prijmy_za_rok_2015 from putrovec.majetkove_priznania order by plat_2015
            )
        ) t1
    GROUP BY yyear;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search