Lånestatistik Bibliotek Mellansjö

Från Svenska kohanätverkets wiki
Hoppa till navigering Hoppa till sök

Statistikrapporter som Bibliotek Mellansjö skapat. Se dem som komplement/alternativ till Hyltes rapporter!

Fråga 10 Fysiskt bestånd

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Bestånd tryckt bok',
  COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Bestånd läromedel',
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Bestånd ljudböcker',
  COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Bestånd talböcker daisy',
  COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Bestånd musik',
  COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Bestånd film',
  COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Bestånd kartor',
  COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Bestånd noter',
  COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Bestånd TV-spel',
  COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Bestånd övrigt'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch = <<Bibliotek|branches>>

Fråga 10 Fysiskt nyförvärv

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Nyförvärv tryckt bok',
  COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Nyförvärv läromedel',
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Nyförvärv ljudböcker',
  COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Nyförvärv talböcker daisy',
  COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Nyförvärv musik',
  COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Nyförvärv film',
  COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Nyförvärv kartor',
  COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Nyförvärv noter',
  COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Nyförvärv TV-spel',
  COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Nyförvärv övrigt'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch = <<Bibliotek|branches>>  AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 


GROUP BY homebranch

Fråga 11 olika bestånd

SELECT 
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL','MUSIKBARN','MUSIKBARN') THEN 1 END) 'Bestånd för barn och unga',
  COUNT(CASE WHEN location IN ('Skonlitteratur','Deckare','Deckare & Thriller','Fantasy','Fantasy/SF','Noveller') THEN 1 END) 'Bestånd av skönlitteratur',
  COUNT(CASE WHEN location IN ('REFERENS') THEN 1 END) 'Antal referensexemplar',
  COUNT(CASE WHEN itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Bestånd läsnedsättning'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

 WHERE items.homebranch = <<Bibliotek|branches>>

Fråga 11 Olika nyförvärv

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)

WHERE items.homebranch IS NOT NULL AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND items.homebranch = <<Bibliotek|branches>>


GROUP BY homebranch

Fråga 11 Olika utlån

SELECT 
  branchname AS Bibliotek,
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn',
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning'  
FROM statistics

LEFT JOIN branches ON (statistics.branch=branches.branchcode)
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE statistics.branch = <<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
 


GROUP BY branch

Fråga 12 Svenska

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal svenska tilar'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)

WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'swe' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '')  AND
items.homebranch=<<Bibliotek|branches>>

Fråga 12 Minoritetsspråk

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal titlar på minoritetsspråk'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)

WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'fin' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'smi' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '9mk' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'rom' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'yid' )  AND
items.homebranch=<<Bibliotek|branches>>

Fråga 12 Utländska

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal utländska titlar'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)

WHERE SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'swe' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'fin' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'smi' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '9mk' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'rom' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'yid' AND
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '' AND
items.homebranch=<<Bibliotek|branches>>

Fråga 14 Lånestatistik

SELECT 
  branchname AS Bibliotek,
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt'

FROM statistics

LEFT JOIN branches ON (statistics.branch=branches.branchcode)
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 


GROUP BY branch

ORDER BY branchname

Fråga 16 Fjärrlån

SELECT 
  branchname,
  COUNT(CASE WHEN type = 'issue' AND borrowers.categorycode IN ('BIBLIOTEK') THEN 1 END) 'Fjärrutlån',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('FJARRLAN') THEN 1 END) 'Fjärrinlån'

FROM statistics

LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 


GROUP BY branch

Fråga 19 Kvinnor

SELECT 
branches.branchname AS Bibliotek,
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' 

FROM statistics 
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) 
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)

WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch

Fråga 19 Män

SELECT 
branches.branchname AS Bibliotek,
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' 

FROM statistics 
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) 
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)

WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch
Hämtad från "https://koha.se/wiki/index.php?title=Lånestatistik_Bibliotek_Mellansjö&oldid=400"