Universes, characters and stories.sql

select uc.universecode             AS "Univers",
       concat(
               SUBSTRING_INDEX(
                       group_concat(distinct character_examples.charactername),
                       ',',
                       5),
               ',...')             AS "Exemples de personnages de cet univers",
       count(distinct uc.charactercode) AS "Nombre de personnages",
       count(distinct s.storycode) AS "Histoires avec au moins un personnage de cet univers"
from inducks_universe u
         inner join inducks_ucrelation uc on u.universecode = uc.universecode
         inner join inducks_appearance ia on uc.charactercode = ia.charactercode
         inner join inducks_storyversion s on ia.storyversioncode = s.storyversioncode
         inner join
     (select c.charactercode, c.charactername
      from inducks_character c
     ) AS character_examples ON character_examples.charactercode = uc.charactercode
group by uc.universecode
order by count(distinct s.storycode) desc;