SELECT
    h.jmeno,
    h1.pocet_minut,
    h1.pocet_zapasu,
    h1.tym_id,
    h2.celkem_golu,
    h2.celkem_golu/h1.pocet_zapasu pgz,
    h2.zk,
    h2.ck,
    n1.url url_hrace,
    t.nazev tym,
    n2.url url_tymu
FROM
  hraci h
LEFT JOIN
      (
        select
            ss.tym_id,
            ss.hrac_id,
            count(minut_celkem) pocet_zapasu,
            sum(minut_celkem)  pocet_minut
        from
            statistiky_stridani ss
        join
            zapasy z
                on z.id = ss.zapas_id
        where
            z.soutez_id = 4
        group by
            ss.hrac_id

      ) h1 ON h.id = h1.hrac_id

  LEFT JOIN
      (

        select
            s.hrac_id,
            sum(s.typ_id = 1 or s.typ_id = 3) as celkem_golu,
            sum(typ_id = 4) as zk,
            sum(typ_id = 5) as ck
        from
             statistiky s
        join
            zapasy z
                on z.id = s.zapas_id
        where
            z.soutez_id = 4
        group by
            s.hrac_id
      ) h2 ON h.id = h2.hrac_id

    join
        navigace n1
            on n1.id = h.nav_id
    join
        tymy t
            on t.id = h1.tym_id
    join
        navigace n2
            on n2.id = t.nav_id
      where
        h1.pocet_minut is not null
      order by
        h2.celkem_golu desc
      LIMIT
            50