To consider only titles classified as games (excluding game compilations), and also discarding crap games from CSSCGC, you can use the following queries...
Total number of games per genre, for either Spectrum 48K or Spectrum 128K models:
Code: Select all
select count(e.id) as n, g.text as genre from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by g.text order by g.text;
Total number of games per year, for either Spectrum 48K or Spectrum 128K models:
Code: Select all
select count(e.id) as n, coalesce(r.release_year,'?') as year from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by r.release_year order by r.release_year;
Total number of games per both year and genre, for either Spectrum 48 or Spectrum 128 models:
Code: Select all
select count(e.id) as n, coalesce(r.release_year,'?') as year, g.text as genre from entries e
inner join releases r on r.entry_id = e.id and r.release_seq = 0
inner join genretypes g on g.id = e.genretype_id
inner join machinetypes m on m.id = e.machinetype_id
where g.text like '%Game:%'
and m.text like 'ZX-Spectrum %' and m.text <> 'ZX-Spectrum Next'
and e.is_crap = 0
group by r.release_year,g.text order by r.release_year,g.text;
If you want these results for games were minimal requirement is Spectrum 128, replace the corresponding condition to this:
Code: Select all
and m.text like 'ZX-Spectrum 128%'
Or to obtain results for ZX81 games:
If you want to exclude "mods" from any of the queries above, then add the following condition:
Finally, if you want to restrict results to games produced using a
Game Creator, then also add the following condition to any of the queries above:
Code: Select all
and e.id in (select entry_id from frameworks f inner join entries u on f.util_id = u.id where u.genretype_id = 52)
It only took me a few minutes to write these queries, but it would take me considerably more time to select, organize and upload each result somewhere. Therefore I will leave
this task as exercise for the reader!