Reports for KB-statistik

The following reports have been created in order to provide statistics from public libraries in Sweden to Kungliga biblioteket. 
The reports were originally created for Hylte public library, and are shared here with their permission. The reports have been edited to remove references to libraries/branches in Hylte. Codes etc used to identify e.g. non-fiction are those used at Hylte. Different libraries might have to adjust this to fit their own setup. 
The years in the reports have been set so that the reports should be ready to report statistics about 2017 in e.g. January 2018. 
There is probably room for improvement here. This is a wiki, so edits are welcome! :-)
== 10 Objekttyper - Antal objekter ==
 SELECT itype, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) <= 2017
 GROUP BY itype
== 10 Objekttyper - Nyförvärv ==
 SELECT itype, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) = 2017
 GROUP BY itype
== 10A: Allting med itemtype: objekttyp böcker - Antal ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'BOK'
 AND YEAR(dateaccessioned) <= 2017
== 10A: Allting med itemtype: objekttyp böcker - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE
 itype = 'BOK'
 AND YEAR(dateaccessioned) = 2017
== 10B: Ljudböcker - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'LJUDBOK'
 AND YEAR(dateaccessioned) <= 2017
== 10B: Ljudböcker - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'LJUDBOK'
 AND YEAR(dateaccessioned) = 2017
== 10C: Talböcker / Daisy - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'TALBOK'
 AND YEAR(dateaccessioned) <= 2017
== 10C: Talböcker / Daisy - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'TALBOK'
 AND YEAR(dateaccessioned) = 2017
== 10D: itemtype Tidningar & Tidskrifter - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'TIDNINGAR'
 AND YEAR(dateaccessioned) <= 2017
== 10D: itemtype Tidningar & Tidskrifter - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'TIDNINGAR'
 AND YEAR(dateaccessioned) = 2017
== 10F: Musik cd - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE
 itype = 'MUSIK'
 AND YEAR(dateaccessioned) <= 2017
== 10F: Musik cd - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'MUSIK'
 AND YEAR(dateaccessioned) = 2017
== 10G: Film - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 ( itype = 'HYRDVD' OR itype = 'MUSIKFILM' )
 AND YEAR(dateaccessioned) <= 2017
== 10G: Film - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 ( itype = 'HYRDVD' OR itype = 'MUSIKFILM' )
 AND YEAR(dateaccessioned) = 2017
== 10H: mikrofilm mm - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'MIKROFILME'
 AND YEAR(dateaccessioned) <= 2017
== 10H: mikrofilm mm - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'MIKROFILME'
 AND YEAR(dateaccessioned) = 2017
== 10I: Kartor mm - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'KARTOR'
 AND YEAR(dateaccessioned) <= 2017
== 10I: Kartor mm - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'KARTOR'
 AND YEAR(dateaccessioned) = 2017
== 10J: Noter mm - Antal objekt ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'NOTER'
 AND YEAR(dateaccessioned) <= 2017
== 10J: Noter mm - Nyförvärv ==
 SELECT COUNT(*)
 FROM items
 WHERE 
 itype = 'NOTER'
 AND YEAR(dateaccessioned) = 2017
== 11-1 Bestånd av tryckta böcker och seriella publikationer för barn & unga ==
 SELECT itype, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) <= 2017
 AND BINARY itemcallnumber LIKE 'u%'
 GROUP BY itype
== 11-2 Antal nyförvärv av tryckta böcker och seriella publikationer för barn & unga ==
 SELECT itype, COUNT(*) AS Antal
 FROM items
 WHERE
 YEAR(dateaccessioned) = 2017
 AND BINARY itemcallnumber LIKE 'u%'
 GROUP BY itype
== 11-3: Antal utlån av tryckta böcker och seriella publikationer för barn & unga ==
 SELECT s.itemtype, i.description, COUNT(*) AS Initiala
 FROM statistics AS s, itemtypes as i, items
 WHERE 
 s.itemtype = i.itemtype
 AND s.itemnumber = items.itemnumber
 AND ( s.type = 'issue' OR s.type = 'renew' )
 AND YEAR(s.datetime) = 2017
 AND BINARY items.itemcallnumber LIKE 'u%'
 GROUP BY s.itemtype
== 11-4: Bestånd av skönlitteratur, tryckta böcker eller seriella publikationer ==
 SELECT itype AS itemtype, COUNT(*) AS Antal
 FROM items
 WHERE 
 ( BINARY LEFT(itemcallnumber,4) LIKE '%H%' OR itemcallnumber LIKE '8%' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itype
== 11-5 Antal referensexemplar ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 notforloan = 1
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 11-6: Bestånd av medier för personer med läsnedsättning ==
 SELECT itype AS itemtype, COUNT(*) AS Antal
 FROM items
 WHERE ( location = 'TALBOKVUXEN' OR 
 location = 'TALBOKBARN' OR 
 location = 'TALBOKTUNN' OR 
 location = 'APPELHYLLAN' OR 
 location = 'BOKDAISY' OR 
 location = 'SPRAKKEDJAN' OR 
 location = 'SPRAKVASKOR' OR 
 location = 'LATTLAST' OR 
 location = 'LLMP3' OR 
 location = 'LLSVE' OR 
 location = 'STORSTIL' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itype
== 11-7: Utlån av medier för personer med läsnedsätting ==
 SELECT i.itype AS itemtype, COUNT(*) AS Antal
 FROM statistics AS s, items AS i
 WHERE 
 s.itemnumber = i.itemnumber
 AND ( location = 'TALBOKVUXEN' OR 
 location = 'TALBOKBARN' OR 
 location = 'TALBOKTUNN' OR 
 location = 'APPELHYLLAN' OR 
 location = 'BOKDAISY' OR 
 location = 'SPRAKKEDJAN' OR 
 location = 'SPRAKVASKOR' OR 
 location = 'LATTLAST' OR 
 location = 'LLMP3' OR 
 location = 'LLSVE' OR 
 location = 'STORSTIL' )
 AND s.type = 'issue'
 GROUP BY itype
== 12a - Antal titlar på svenska språket ==
 SELECT SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) AS Språk, count(*) AS Antal
 FROM biblioitems
 GROUP BY Språk HAVING Språk = 'swe'
== 12b - Antal titlar på nationellt minoritetsspråk ==
 SELECT SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) AS Språk, count(*) AS Antal
 FROM biblioitems
 GROUP BY Språk HAVING ( Språk = 'fin' OR Språk = 'smi' OR Språk = '9mk' OR Språk = 'rom' OR Språk = 'yid' )
== 12c - Antal titlar på utländska språk ==
 SELECT COUNT(*) AS Antal
 FROM biblioitems
 WHERE 
 SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != 'swe'
 AND SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != 'fin'
 AND SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != 'smi'
 AND SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != '9mk'
 AND SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != 'rom'
 AND SUBSTRING(ExtractValue(marcxml, '//controlfield[@tag="008"]'), 36, 3) != 'yid'
== 14 - Initiala lån ==
 SELECT s.itemtype, i.description, COUNT(*) AS Initiala
 FROM statistics AS s, itemtypes as i
 WHERE 
 s.itemtype = i.itemtype
 AND s.type = 'issue'
 AND YEAR(s.datetime) = 2017
 GROUP BY s.itemtype
== 14 - Omlån ==
 SELECT s.itemtype, i.description, COUNT(*) AS Initiala
 FROM statistics AS s, itemtypes as i
 WHERE 
 s.itemtype = i.itemtype
 AND s.type = 'renew'
 AND YEAR(s.datetime) = 2017
 GROUP BY s.itemtype
== 14 - Totala utlån ==
 SELECT s.itemtype, i.description, COUNT(*) AS Initiala
 FROM statistics AS s, itemtypes as i
 WHERE 
 s.itemtype = i.itemtype
 AND ( s.type = 'issue' OR s.type = 'renew' )
 AND YEAR(s.datetime) = 2017
 GROUP BY s.itemtype
== 18 Fack- och referenslitteratur - Bestånd för barn ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 BINARY itemcallnumber NOT LIKE '%H%'
 AND ( BINARY itemcallnumber LIKE 'u%' OR BINARY itemcallnumber LIKE 'uf%' OR BINARY itemcallnumber LIKE 'ug%' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 18 Fack- och referenslitteratur - Bestånd för barn - lista ==
 SELECT itemcallnumber, COUNT(*) AS Antal
 FROM items
 WHERE 
 BINARY itemcallnumber NOT LIKE '%H%'
 AND ( BINARY itemcallnumber LIKE 'u%' OR BINARY itemcallnumber LIKE 'uf%' OR BINARY itemcallnumber LIKE 'ug%' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itemcallnumber
== 18 Fack- och referenslitteratur - Bestånd för vuxna ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 BINARY itemcallnumber NOT LIKE '%H%'
 AND BINARY itemcallnumber NOT LIKE 'u%'
 AND BINARY itemcallnumber NOT LIKE 'uf%'
 AND BINARY itemcallnumber NOT LIKE 'ug%'
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 18 Fack- och referenslitteratur - Bestånd för vuxna - lista ==
 SELECT itemcallnumber, COUNT(*) AS Antal
 FROM items
 WHERE 
 BINARY itemcallnumber NOT LIKE '%H%'
 AND BINARY itemcallnumber NOT LIKE 'u%'
 AND BINARY itemcallnumber NOT LIKE 'uf%'
 AND BINARY itemcallnumber NOT LIKE 'ug%'
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itemcallnumber
== 18 Fack- och referenslitteratur - Nyförvärv för barn ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 dateaccessioned LIKE '2013-%'
 AND ( BINARY itemcallnumber NOT LIKE '%H%'
 AND ( BINARY itemcallnumber LIKE 'u%' OR BINARY itemcallnumber LIKE 'uf%' OR BINARY itemcallnumber LIKE 'ug%' ) )
 AND YEAR(dateaccessioned) = 2017
 GROUP BY homebranch
== 18 Fack- och referenslitteratur - Nyförvärv för vuxna ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 dateaccessioned LIKE '2013-%'
 AND ( BINARY itemcallnumber NOT LIKE '%H%'
 AND BINARY itemcallnumber NOT LIKE 'u%'
 AND BINARY itemcallnumber NOT LIKE 'uf%'
 AND BINARY itemcallnumber NOT LIKE 'ug%' )
 AND YEAR(dateaccessioned) = 2017
 GROUP BY homebranch
== 18 Skönlitteratur - Bestånd för barn ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 itemcallnumber NOT LIKE '% %'
 AND ( BINARY itemcallnumber LIKE 'uH%' OR 
 BINARY itemcallnumber LIKE 'ufH%' OR 
 BINARY itemcallnumber LIKE 'ugH%' OR 
 BINARY itemcallnumber LIKE 'Hcg%' OR 
 BINARY itemcallnumber LIKE 'Hcf%' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 18 Skönlitteratur - Bestånd för barn - lista ==
 SELECT itemcallnumber, COUNT(*) AS Antal
 FROM items
 WHERE 
 itemcallnumber NOT LIKE '% %'
 AND ( BINARY itemcallnumber LIKE 'uH%' OR 
 BINARY itemcallnumber LIKE 'ufH%' OR 
 BINARY itemcallnumber LIKE 'ugH%' OR 
 BINARY itemcallnumber LIKE 'Hcg%' OR 
 BINARY itemcallnumber LIKE 'Hcf%' )
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itemcallnumber
== 18 Skönlitteratur - Bestånd för vuxna ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 itemcallnumber NOT LIKE '% %'
 AND BINARY itemcallnumber LIKE '%H%'
 AND itemcallnumber != 'Hcg'
 AND itemcallnumber != 'Hcf'
 AND BINARY itemcallnumber NOT LIKE 'u%'
 AND BINARY itemcallnumber NOT LIKE 'uf%'
 AND BINARY itemcallnumber NOT LIKE 'ug%'
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 18 Skönlitteratur - Bestånd för vuxna - lista ==
 SELECT itemcallnumber, count(*) AS Antal
 FROM items
 WHERE 
 itemcallnumber NOT LIKE '% %' -- Må ikke innehole mellomrom
 AND BINARY itemcallnumber LIKE '%H%' -- Må inneholde en stor H
 AND itemcallnumber != 'Hcg' -- Må ikke være Hcg
 AND itemcallnumber != 'Hcf' -- Må ikke være HCF
 AND BINARY itemcallnumber NOT LIKE 'u%' -- Må ikke starte på liten u
 AND BINARY itemcallnumber NOT LIKE 'uf%' -- Må ikke starte på liten uf
 AND BINARY itemcallnumber NOT LIKE 'ug%' -- Må ikke starte på liten ug
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itemcallnumber
== 18 Skönlitteratur - Nyförvärv för barn ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) = 2017
 AND ( itemcallnumber NOT LIKE '% %'
 AND ( BINARY itemcallnumber LIKE 'uH%' OR 
 BINARY itemcallnumber LIKE 'ufH%' OR 
 BINARY itemcallnumber LIKE 'ugH%' OR 
 BINARY itemcallnumber LIKE 'Hcg%' OR 
 BINARY itemcallnumber LIKE 'Hcf%' ) )
 GROUP BY homebranch
== 18 Skönlitteratur - Nyförvärv för vuxna ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) = 2017
 AND ( itemcallnumber NOT LIKE '% %'
 AND BINARY itemcallnumber LIKE '%H%'
 AND itemcallnumber != 'Hcg'
 AND itemcallnumber != 'Hcf'
 AND BINARY itemcallnumber NOT LIKE 'u%'
 AND BINARY itemcallnumber NOT LIKE 'uf%'
 AND BINARY itemcallnumber NOT LIKE 'ug%' )
 GROUP BY homebranch
== 19a - Antal aktiva låntagare som är kvinnor ==
 SELECT COUNT(DISTINCT s.borrowernumber) AS Antal
 FROM statistics AS s, borrower_attributes AS ba
 WHERE 
 s.borrowernumber = ba.borrowernumber
 AND s.datetime LIKE '2017-%'
 AND code = 'PRNSNR'
 AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0
== 19b - Antal aktiva låntagare som är män ==
 SELECT COUNT(DISTINCT s.borrowernumber) AS Antal
 FROM statistics AS s, borrower_attributes AS ba
 WHERE 
 s.borrowernumber = ba.borrowernumber
 AND s.datetime LIKE '2017-%'
 AND code = 'PRNSNR'
 AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1
== 19c - Antal aktiva låntagare som är institutionslåntagare eller personer som saknar uppgift om personnummer ==
 SELECT COUNT(DISTINCT s.borrowernumber) AS Antal_aktiva
 FROM statistics AS s LEFT JOIN borrower_attributes AS ba ON s.borrowernumber = ba.borrowernumber
 WHERE 
 s.datetime LIKE '2017-%'
 AND ba.attribute IS NULL
== 19d - Totalt antal aktiva låntagare ==
 SELECT COUNT(DISTINCT borrowernumber) AS Antal_aktiva
 FROM statistics
 WHERE 
 datetime LIKE '2017-%'
== 19e - Aktiva låntagare som är under 18 år ==
 SELECT COUNT(DISTINCT s.borrowernumber) AS Antal_aktiva
 FROM statistics AS s, borrowers AS b
 WHERE 
 s.borrowernumber = b.borrowernumber
 AND b.dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR)
 AND s.datetime LIKE '2017-%'
== 20 nyförvärvade titler - eksemplar (hele året) ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 YEAR(dateaccessioned) = 2017
 GROUP BY homebranch
== 20 nyförvärvade titler - poster ==
 SELECT COUNT(*) AS Antal
 FROM biblio
 WHERE 
 YEAR(dateaccessioned) = 2017
== 21 Bestånd av AV-medier per avdelning ==
 SELECT itemtype, description, COUNT(*) AS Antal
 FROM itemtypes AS t, items AS i
 WHERE 
 t.itemtype = i.itype
 AND itemtype != 'A'
 AND itemtype != 'FJ'
 AND itemtype != 'C'
 AND itemtype != 'DEP'
 AND itemtype != 'E'
 AND itemtype != 'N'
 AND itemtype != 'H'
 AND itemtype != 'TIDN'
 AND homebranch = <>
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY itemtype
== 21 Nyförvärv av AV-medier ==
 SELECT itemtype, description, COUNT(*) AS Antal
 FROM itemtypes AS t, items AS i
 WHERE 
 t.itemtype = i.itype
 AND itemtype != 'A'
 AND itemtype != 'C'
 AND itemtype != 'DEP'
 AND itemtype != 'E'
 AND itemtype != 'N'
 AND itemtype != 'H'
 AND itemtype != 'TIDN'
 AND YEAR(i.dateaccessioned) = 2017
 GROUP BY itemtype
== 25 Beståndet av anpassade medier ==
 SELECT homebranch AS Avdelning, COUNT(*) AS Antal
 FROM items
 WHERE 
 itype = 'TALBOK'
 AND YEAR(dateaccessioned) <= 2017
 GROUP BY homebranch
== 25 Utlåningen av anpassade medier ==
 SELECT branch AS Avdelning, COUNT(*) AS Antal
 FROM statistics
 WHERE 
 YEAR(datetime) = '2017'
 AND itemtype = 'TALBOK'
 AND ( type = 'issue' OR type = 'renew' )
 GROUP BY branch
== 27 Utlån - Fack- och referenslitteratur för barn ==
 SELECT branch AS Avdelning, COUNT(*) AS Antal
 FROM statistics, items
 WHERE 
 statistics.itemnumber = items.itemnumber
 AND YEAR(statistics.datetime) = '2017'
 AND ( statistics.type = 'issue' OR statistics.type = 'renew' )
 AND BINARY items.itemcallnumber NOT LIKE '%H%'
 AND ( BINARY items.itemcallnumber LIKE 'u%' OR BINARY items.itemcallnumber LIKE 'uf%' OR BINARY items.itemcallnumber LIKE 'ug%' )
 GROUP BY branch
== 27 Utlån - Fack- och referenslitteratur för vuxna ==
 SELECT branch AS Avdelning, COUNT(*) AS Antal
 FROM statistics, items
 WHERE 
 statistics.itemnumber = items.itemnumber
 AND YEAR(statistics.datetime) = '2017'
 AND ( statistics.type = 'issue' OR statistics.type = 'renew' )
 AND BINARY items.itemcallnumber NOT LIKE '%H%'
 AND BINARY items.itemcallnumber NOT LIKE 'u%'
 AND BINARY items.itemcallnumber NOT LIKE 'uf%'
 AND BINARY items.itemcallnumber NOT LIKE 'ug%'
 GROUP BY branch
== 27 Utlån - Skönlitteratur för barn ==
 SELECT branch AS Avdelning, COUNT(*) AS Antal
 FROM statistics, items
 WHERE 
 statistics.itemnumber = items.itemnumber
 AND YEAR(statistics.datetime) = '2017'
 AND ( type = 'issue' OR type = 'renew' )
 AND items.itemcallnumber NOT LIKE '% %'
 AND ( BINARY items.itemcallnumber LIKE 'uH%' OR 
 BINARY items.itemcallnumber LIKE 'ufH%' OR 
 BINARY items.itemcallnumber LIKE 'ugH%' OR 
 BINARY items.itemcallnumber LIKE 'Hcg%' OR 
 BINARY items.itemcallnumber LIKE 'Hcf%' )
 GROUP BY branch
== 27 Utlån - Skönlitteratur för vuxna ==
 SELECT branch AS Avdelning, COUNT(*) AS Antal
 FROM statistics, items
 WHERE 
 statistics.itemnumber = items.itemnumber
 AND YEAR(datetime) = '2017'
 AND ( statistics.type = 'issue' OR statistics.type = 'renew' )
 AND items.itemcallnumber NOT LIKE '% %'
 AND BINARY items.itemcallnumber LIKE '%H%'
 AND items.itemcallnumber != 'Hcg'
 AND items.itemcallnumber != 'Hcf'
 AND BINARY items.itemcallnumber NOT LIKE 'u%'
 AND BINARY items.itemcallnumber NOT LIKE 'uf%'
 AND BINARY items.itemcallnumber NOT LIKE 'ug%'
 GROUP BY branch
== 28 Utlån av AV-medier ==
 SELECT statistics.itemtype AS Typ, itemtypes.description AS Beskrivning, COUNT(*) AS Antal
 FROM statistics, itemtypes
 WHERE 
 statistics.itemtype = itemtypes.itemtype
 AND statistics.itemtype != 'A'
 AND statistics.itemtype != 'C'
 AND statistics.itemtype != 'FJ'
 AND statistics.itemtype != 'DEP'
 AND statistics.itemtype != 'E'
 AND statistics.itemtype != 'N'
 AND statistics.itemtype != 'H'
 AND statistics.itemtype != 'TIDN'
 AND YEAR(statistics.datetime) = '2017'
 GROUP BY statistics.itemtype
== 28 Utlåningen av AV-medier - aktive lån ==
 SELECT items.itype, COUNT(*) AS Laan, SUM(issues.renewals) AS Omlaan
 FROM issues LEFT JOIN items ON issues.itemnumber = items.itemnumber
 WHERE 
 itype != 'A'
 AND itype != 'C'
 AND itype != 'DEP'
 AND itype != 'E'
 AND itype != 'N'
 AND itype != 'H'
 AND itype != 'TIDN'
 GROUP BY itype
== 28 Utlåningen av AV-medier - gamla lån ==
 SELECT items.itype, COUNT(*) AS Laan, SUM(old_issues.renewals) AS Omlaan
 FROM old_issues LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
 WHERE 
 itype != 'A'
 AND itype != 'C'
 AND itype != 'DEP'
 AND itype != 'E'
 AND itype != 'N'
 AND itype != 'H'
 AND itype != 'TIDN'
 GROUP BY itype
Category:Sweden