Number of items by type:

select type, count(*)
from eprint
where eprint_status='archive'
group by type
order by type;

Same, but for theses only:

select thesis_type, count(*)
from eprint
where eprint_status='archive' and type = 'thesis'
group by thesis_type
order by thesis_type;

Same, but added by month:

select datestamp_year, datestamp_month, type, count(*)
from eprint
where eprint_status='archive'
group by datestamp_year, datestamp_month, type
order by datestamp_year, datestamp_month, type;


Number of items with full text (all/theses):

select type, count(distinct eprintid)
from eprint inner join document using (eprintid)
where eprint_status='archive' and security <> 'staffonly'
group by type
order by type;

select thesis_type, count(distinct eprintid)
from eprint inner join document using (eprintid)
where eprint_status='archive' and security <> 'staffonly' and type = 'thesis'
group by thesis_type
order by thesis_type;

Same, but the full text is publicly available:

select type, count(distinct eprintid)
from eprint inner join document using (eprintid)
where eprint_status='archive' and security = 'public'
group by type
order by type;

select thesis_type, count(distinct eprintid)
from eprint inner join document using (eprintid)
where eprint_status='archive' and security = 'public' and type = 'thesis'
group by thesis_type
order by thesis_type;


Number of items affiliated with each organisational unit (all items):

select affiliations, count(*)
from eprint inner join eprint_affiliations using (eprintid)
where eprint_status='archive'
group by affiliations
order by affiliations;


Downloads and abstract views by country (excluding search engines):

select country_name, view_type, count(*)
from view
where archive_name='otago_eprints' and view_type='download'
  and country_code not like 'X@%'
group by country_name, view_type
order by country_name, view_type;

Downloads by search engine:

select country_name, count(*)
from view
where archive_name='otago_eprints' and view_type='download'
  and country_code like 'X@%'
group by country_name
order by country_name;


Items by author:

select creators_name_family, creators_name_given, count(*)
from eprint inner join eprint_creators_name using (eprintid)
            inner join eprint_affiliations using (eprintid)
where affiliations in ('INFO', 'ACCT', 'MANT', 'MART', 'ECON', 'TOUR', 'FINC', 'SoB_Dean')
  and eprint_status = 'archive'
group by creators_name_family, creators_name_given
order by creators_name_family, creators_name_given;



Abstract/download views by month:

select year(request_date) as yyyy, month(request_date) as mm, count(*)
from view
where view_type = 'abstract'
  and archive_name = 'otago_eprints'
group by yyyy, mm
order by yyyy, mm;

select year(request_date) as yyyy, month(request_date) as mm, count(*)
from view
where view_type = 'download'
  and archive_name = 'otago_eprints'
group by yyyy, mm
order by yyyy, mm;