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