Skip to content

Instantly share code, notes, and snippets.

@bperel

bperel/Universes, characters and stories.sql

Created November 1, 2020 12:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bperel/3f1e42206170a52a159563beef1846d2 to your computer and use it in GitHub Desktop.
Save bperel/3f1e42206170a52a159563beef1846d2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment