User:Qshadow

Stats scripts

1. Get All Authors

SELECT DISTINCT a.author_id, a.author_canonical, MIN(DATE_FORMAT(t.title_copyright,'%Y')), MAX(DATE_FORMAT(t.title_copyright,'%Y')) FROM titles t, canonical_author ca, authors a WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" GROUP BY a.author_id INTO OUTFILE 'C:/ISFDB_AllAuthors.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

1. Get All Titles

SELECT DISTINCT a.author_id, a.author_canonical, t.title_id, t.title_title, t.title_ttype, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views FROM canonical_author ca, authors a, titles t WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" AND t.title_parent = '0' # This is the canonical name AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') INTO OUTFILE 'C:/ISFDB_TitlesBest.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';

1. !!! Get All titles that have Awards

SELECT DISTINCT a.author_id, a.author_canonical, IF(t.title_parent=0,t.title_id,t.title_parent), t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views, awards.award_id, DATE_FORMAT(awards.award_year,'%Y'), awards.award_ttype, awards.award_atype, awards.award_level FROM titles t, awards, title_awards, canonical_author ca, authors a WHERE t.title_id = title_awards.title_id AND awards.award_id = title_awards.award_id AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND ca.title_id = t.title_id AND a.author_id = ca.author_id INTO OUTFILE 'C:/ISFDB_TitleAwards.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';

1. !!! Get All Awards

SELECT DISTINCT awards.award_id, DATE_FORMAT(awards.award_year,'%Y') FROM awards WHERE DATE_FORMAT(awards.award_year,'%Y') < "1990" INTO OUTFILE 'C:/Awards.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';

1. !!! Get Magazine titles

SELECT DISTINCT DATE_FORMAT(p.pub_year,'%Y.%m'), p.pub_id, p.pub_title, t.title_id, t.title_parent, IF(t.title_parent=0,t.title_id,t.title_parent), t.title_title, a.author_id, a.author_canonical, t.title_ttype, p.pub_ptype, t.title_views, a.author_views FROM pubs p, pub_content pc, authors a, titles t, canonical_author ca WHERE p.pub_ctype = 'MAGAZINE' AND ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_ttype IN ('NOVEL','SHORTFICTION','SERIAL') AND p.pub_id = pc.pub_id AND t.title_id = pc.title_id AND DATE_FORMAT(p.pub_year,'%Y') < "1990" INTO OUTFILE 'C:/MagazinesContents.txt' FIELDS TERMINATED BY '^' ENCLOSED BY '"' LINES TERMINATED BY '\n';

1. Get All Anthologies Titles

SELECT DISTINCT a.author_id, a.author_canonical, t.title_id, t.title_title, DATE_FORMAT(t.title_copyright,'%Y'), t.title_views FROM canonical_author ca, authors a, titles t WHERE ca.title_id = t.title_id AND a.author_id = ca.author_id AND t.title_parent = '0' # This is the canonical name of the Anthology AND t.title_ttype IN ('ANTHOLOGY','OMNIBUS') AND DATE_FORMAT(t.title_copyright,'%Y') < "1990" INTO OUTFILE 'C:/AntholgiesTitles.txt' FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';