Skillnad mellan versioner av "Nyckeltal för samlingarna Bibliotek Mellansjö"

Från Svenska kohanätverkets wiki
Hoppa till navigering Hoppa till sök
(Skapade sidan med '<pre> SELECT CASE WHEN ds.ccode IS NULL THEN '--' ELSE ds.ccode END AS Avdelning, CASE WHEN authorised_values.lib IS NULL THEN '--' ELSE authorised_values.lib END AS Placer...')
 
 
(En mellanliggande sidversion av samma användare visas inte)
Rad 1: Rad 1:
 +
 +
== Nyttjandegrad ==
 +
 +
Nyttjandegrad=Lån per exemplar under det senaste året
 +
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
−
CASE
+
branchname AS Bibliotek, Avdelning, Placering, Utlan,  
−
WHEN ds.ccode IS NULL
+
Exemplar AS 'Egna exemplar',  
−
THEN '--'
+
ROUND(
−
ELSE
+
(Utlan/Exemplar)
−
ds.ccode
+
,1)
−
END
+
AS Nyttjandegrad
−
AS Avdelning,
+
 
−
CASE
+
FROM
−
WHEN authorised_values.lib IS NULL
+
(SELECT statistics.branch, statistics.ccode AS Avdelning, authorised_values.lib AS Placering, COUNT(statistics.type) AS Utlan,
−
THEN '--'
 
−
ELSE
 
−
authorised_values.lib
 
−
END
 
−
AS Placering,
 
−
SUM(issue1) AS 'Utl på hembibl',
 
−
SUM(issue2) AS 'Utl på annat bibl',
 
−
SUM(issue3) AS 'Utl totalt',
 
−
SUM(exemplar) AS 'Exemplar',
 
−
ROUND(SUM(issue3) / sum(exemplar),2)
 
−
as Kvot,
 
−
ROUND(100*(SUM(issue3) /
 
−
(SELECT COUNT(*)
 
−
FROM statistics
 
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
 
−
WHERE type='issue'
 
−
AND items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND statistics.location LIKE <<Placering|LOC:all>>
 
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND statistics.datetime >= curdate() - interval 1 year )),2) AS '% av utlån',
 
−
ROUND(100*(SUM(exemplar) /
 
 
(SELECT COUNT (*)
 
(SELECT COUNT (*)
−
FROM items  
+
FROM items
 +
 
 +
WHERE items.homebranch=statistics.branch
 +
AND items.ccode=statistics.ccode
 +
AND items.location=statistics.location) AS Exemplar
 +
 
 +
FROM statistics
 +
LEFT JOIN authorised_values ON (statistics.location=authorised_values.authorised_value)
 +
 
 
WHERE  
 
WHERE  
−
items.homebranch LIKE <<Bibliotek|branches:all>>
+
statistics.branch LIKE <<Bibliotek|branches>>
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND statistics.ccode LIKE  <<Avdelning|CCODE:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND statistics.location LIKE <<Placering|LOC:all>>  
−
)),2)
+
AND statistics.itemtype LIKE <<Exemplartyp |itemtypes:all>>
−
AS '% av exemplar',
+
AND statistics.type='issue'
−
ROUND(100*(SUM(issue3) /
+
 
−
(SELECT COUNT(*)  
+
GROUP BY branch,Avdelning,Placering
−
FROM statistics
+
ORDER BY branch,ccode,location)t
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
+
 
−
WHERE type='issue'
+
LEFT JOIN branches ON (t.branch=branches.branchcode)
−
AND items.homebranch LIKE <<Bibliotek|branches:all>>  
+
</pre>
−
AND items.location LIKE <<Placering|LOC:all>>
+
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
 
−
AND statistics.datetime >= curdate() - interval 1 year ))
+
== Diverse medelvärden ==
−
-
+
 
−
100*(SUM(exemplar) /
+
Säger förhoppningsvis något om hur aktuellt, använt och slitet beståndet är
−
(SELECT COUNT (*)
+
 
−
FROM items
+
<pre>
−
WHERE
+
SELECT branchname,ccode AS Avdelning,authorised_values.lib AS Placering,
−
items.homebranch LIKE <<Bibliotek|branches:all>>
+
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(dateaccessioned))),1,10) AS 'Förvärvad',
−
AND items.location LIKE <<Placering|LOC:all>>  
+
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(datelastborrowed))),1,10) AS 'Senast lånad',
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
ROUND(AVG(issues),1) AS 'Antal lån sedan boken köptes'
−
)),2)
+
 
−
AS Performance,
+
 
−
ROUND(100*(SUM(ckoinperiod)/SUM(exemplar)),2) '% ex lånade senaste året',
+
FROM items
−
ROUND(100*(SUM(ckonow)/SUM(exemplar)),2) AS '% Utlånat just nu',
+
 
−
ROUND(100*((SUM(ckoinperiod)-SUM(ckonow))/SUM(exemplar)),2) AS '% ex tillg just nu men lånade senaste året',
+
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
−
ROUND(SUM(issue3) / sum(ckoinperiod),2)
+
LEFT JOIN authorised_values ON (items.location=authorised_values.authorised_value)
−
as 'Kvot för lånade ex',
+
 
−
SUM(acqinperiod) AS 'Antal förvärvade senaste året',
+
WHERE dateaccessioned IS NOT NULL
−
ROUND(100*(SUM(acqinperiod)/SUM(exemplar)),2) AS '% ex förvärvade senaste året',
+
AND datelastborrowed IS NOT NULL
−
SUM(weedinperiod) AS 'Antal gallrade senaste året',
+
AND homebranch LIKE <<Bibliotek|branches>>  
−
ROUND(100*(SUM(weedinperiod)/(SUM(exemplar)+SUM(weedinperiod)-SUM(acqinperiod))),2) AS '% ex gallrat av beståndet för ett år sedan',
+
AND ccode LIKE <<Avdelning|CCODE:all>>  
−
SUM(acqinperiod)-SUM(weedinperiod) AS 'Förändring',
+
AND location LIKE <<Placering|LOC:all>>  
−
FORMAT(AVG(totissues),2) AS 'Genomsnittligt antal lån sedan förvärv',
+
AND itype LIKE <<Exemplartyp |itemtypes:all>>  
−
SUBSTRING(AVG(accyear),1,4) AS 'Genomsnittligt förvärvsår'
+
 
 +
 
 +
GROUP BY homebranch,ccode,location
 +
ORDER BY homebranch,ccode,location
 +
</pre>
 +
 
 +
 
 +
== Förändringar av beståndet ==
  
 +
Antal nyförvärv och gallringar under det senaste året
  
 +
<pre>
 +
SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering, SUM(Forvarv) AS 'Förvärvat', SUM(Gallrat) AS 'Gallrat', SUM(Forvarv)-SUM(Gallrat) AS 'Förändring'
 
FROM
 
FROM
−
((SELECT statistics.ccode, statistics.location, 1 as issue1, 0 as issue2, 1 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
+
(
−
FROM statistics
+
SELECT homebranch,ccode,location,COUNT(*) AS Forvarv, 0 AS Gallrat
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
+
FROM items
−
WHERE type='issue' AND items.homebranch LIKE <<Bibliotek|branches:all>>  
+
WHERE items.dateaccessioned >= curdate() - interval 1 year
−
AND statistics.location LIKE <<Placering|LOC:all>>  
+
AND items.homebranch LIKE <<Bibliotek|branches>>  
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND items.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.homebranch=statistics.branch
+
AND items.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND items.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND statistics.datetime >= curdate() - interval 1 year
+
GROUP BY homebranch,ccode,location
−
)
+
 
−
UNION ALL
+
UNION
−
(SELECT items.ccode, statistics.location, 0 as issue1, 1 as issue2, 1 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
+
 
−
FROM statistics
+
SELECT homebranch,ccode,location,0 'Förvärv', COUNT(*) AS Gallrat
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
+
FROM deleteditems
−
WHERE type='issue' AND items.homebranch LIKE <<Bibliotek|branches:all>>  
+
WHERE deleteditems.timestamp >= curdate() - interval 1 year
−
AND statistics.location LIKE <<Placering|LOC:all>>  
+
AND deleteditems.homebranch LIKE <<Bibliotek|branches>>
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND deleteditems.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.homebranch!=statistics.branch
+
AND deleteditems.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND deleteditems.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND statistics.datetime >= curdate() - interval 1 year
+
GROUP BY homebranch,ccode,location
−
)
+
) t
−
UNION ALL
+
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 1 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, items.issues as totissues, items.dateaccessioned as accyear, 0 as ckonow, 0 as weedinperiod
+
LEFT JOIN branches ON (t.homebranch=branches.branchcode)
 +
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)
 +
 
 +
GROUP BY homebranch,ccode,location
 +
ORDER BY homebranch,ccode,location
 +
</pre>
 +
 
 +
 
 +
== Hyllvärmare och utlånade exemplar==
 +
 
 +
Bibliotek Mellansjös medieplan säger att böcker ska gallras eller magasineras om de inte har varit utlånade på tre år - siffran i den kolumnen visar alltså hur mycket som blir kvar om man följer det som planen säger
 +
 
 +
<pre>
 +
SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering,SUM(Exemplar) AS Exemplar, SUM(Hyllvarmare) AS 'Aldrig utlånat', CONCAT(ROUND(100*(SUM(Hyllvarmare)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlyear) AS 'Utlånat senaste tre åren', CONCAT(ROUND(100*(SUM(Utlyear)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlanat) AS 'Utlånat just nu', CONCAT(ROUND(100*(SUM(Utlanat)/SUM(Exemplar)),0), ' %') AS ''
 +
 
 +
FROM
 +
(SELECT homebranch,ccode,location,COUNT(*) AS Exemplar, 0 AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
 
FROM items
 
FROM items
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
+
WHERE items.homebranch LIKE <<Bibliotek|branches>>  
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND items.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND items.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND items.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
)
+
GROUP BY homebranch,ccode,location
−
UNION ALL
+
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 1 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
+
UNION  
 +
 
 +
SELECT homebranch,ccode,location,0 AS Exemplar, COUNT(*) AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
 
FROM items
 
FROM items
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
+
WHERE items.homebranch LIKE <<Bibliotek|branches>>  
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND items.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND items.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND items.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND items.datelastborrowed >= curdate() - interval 1 year
+
AND issues='0'
−
)
+
GROUP BY homebranch,ccode,location
−
UNION ALL
+
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 1 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
+
UNION
 +
 
 +
SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, COUNT(*) AS Utlyear, 0 AS Utlanat
 
FROM items
 
FROM items
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
+
WHERE items.homebranch LIKE <<Bibliotek|branches>>  
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND items.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND items.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND items.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND items.dateaccessioned >= curdate() - interval 1 year
+
AND items.datelastborrowed >= curdate() - interval 3 year  
−
)
+
GROUP BY homebranch,ccode,location
  
−
UNION ALL
+
UNION  
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 1 as ckonow, 0 as weedinperiod
+
 
 +
SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, 0 Utlyear, COUNT(*) AS Utlanat
 
FROM items
 
FROM items
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
+
WHERE items.homebranch LIKE <<Bibliotek|branches>>  
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND items.ccode LIKE <<Avdelning|CCODE:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND items.location LIKE <<Placering|LOC:all>>  
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
+
AND items.itype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND items.onloan IS NOT NULL
+
AND onloan IS NOT NULL
−
)
+
GROUP BY homebranch,ccode,location) t
  
−
UNION ALL
+
LEFT JOIN branches ON (t.homebranch=branches.branchcode)
−
(SELECT deleteditems.ccode,deleteditems.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 1 as weedinperiod
+
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)
−
FROM deleteditems
 
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND deleteditems.location LIKE <<Placering|LOC:all>>
 
−
AND deleteditems.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND deleteditems.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND deleteditems.timestamp >= curdate() - interval 1 year
 
−
)
 
−
) ds
 
−
LEFT JOIN authorised_values ON (authorised_values.authorised_value=ds.location)
 
−
WHERE ds.location LIKE <<Placering|LOC:all>>
 
−
AND ds.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
GROUP BY ds.ccode,authorised_values.lib WITH ROLLUP
 
  
 +
GROUP BY homebranch,ccode,location
 +
ORDER BY homebranch,ccode,location
 
</pre>
 
</pre>

Nuvarande version från 8 mars 2023 kl. 16.51

Nyttjandegrad

Nyttjandegrad=Lån per exemplar under det senaste året 

SELECT 
branchname AS Bibliotek, Avdelning, Placering, Utlan, 
Exemplar AS 'Egna exemplar', 
ROUND(
(Utlan/Exemplar)
,1)
AS Nyttjandegrad

FROM
(SELECT statistics.branch, statistics.ccode AS Avdelning, authorised_values.lib AS Placering, COUNT(statistics.type) AS Utlan,
(SELECT COUNT (*)
FROM items

WHERE items.homebranch=statistics.branch
AND items.ccode=statistics.ccode
AND items.location=statistics.location) AS Exemplar

FROM statistics
LEFT JOIN authorised_values ON (statistics.location=authorised_values.authorised_value)

WHERE 
statistics.branch LIKE  <<Bibliotek|branches>> 
AND statistics.ccode LIKE  <<Avdelning|CCODE:all>> 
AND statistics.location LIKE  <<Placering|LOC:all>> 
AND statistics.itemtype LIKE  <<Exemplartyp |itemtypes:all>> 
AND statistics.type='issue'

GROUP BY branch,Avdelning,Placering
ORDER BY branch,ccode,location)t

LEFT JOIN branches ON (t.branch=branches.branchcode)


Diverse medelvärden

Säger förhoppningsvis något om hur aktuellt, använt och slitet beståndet är 

SELECT branchname,ccode AS Avdelning,authorised_values.lib AS Placering,
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(dateaccessioned))),1,10) AS 'Förvärvad',
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(datelastborrowed))),1,10) AS 'Senast lånad',
ROUND(AVG(issues),1) AS 'Antal lån sedan boken köptes'


FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (items.location=authorised_values.authorised_value)

WHERE dateaccessioned IS NOT NULL
AND datelastborrowed IS NOT NULL
AND homebranch LIKE  <<Bibliotek|branches>> 
AND ccode LIKE  <<Avdelning|CCODE:all>> 
AND location LIKE  <<Placering|LOC:all>> 
AND itype LIKE <<Exemplartyp |itemtypes:all>> 


GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location


Förändringar av beståndet

Antal nyförvärv och gallringar under det senaste året 

SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering, SUM(Forvarv) AS 'Förvärvat', SUM(Gallrat) AS 'Gallrat', SUM(Forvarv)-SUM(Gallrat) AS 'Förändring'
FROM
(
SELECT homebranch,ccode,location,COUNT(*) AS Forvarv, 0 AS Gallrat
FROM items
WHERE items.dateaccessioned >= curdate() - interval 1 year
AND items.homebranch LIKE <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location

UNION

SELECT homebranch,ccode,location,0 'Förvärv', COUNT(*) AS Gallrat
FROM deleteditems
WHERE deleteditems.timestamp >= curdate() - interval 1 year
AND deleteditems.homebranch LIKE <<Bibliotek|branches>>
AND deleteditems.ccode LIKE  <<Avdelning|CCODE:all>> 
AND deleteditems.location LIKE  <<Placering|LOC:all>> 
AND deleteditems.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location
) t

LEFT JOIN branches ON (t.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)

GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location


Hyllvärmare och utlånade exemplar

Bibliotek Mellansjös medieplan säger att böcker ska gallras eller magasineras om de inte har varit utlånade på tre år - siffran i den kolumnen visar alltså hur mycket som blir kvar om man följer det som planen säger 

SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering,SUM(Exemplar) AS Exemplar, SUM(Hyllvarmare) AS 'Aldrig utlånat', CONCAT(ROUND(100*(SUM(Hyllvarmare)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlyear) AS 'Utlånat senaste tre åren', CONCAT(ROUND(100*(SUM(Utlyear)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlanat) AS 'Utlånat just nu', CONCAT(ROUND(100*(SUM(Utlanat)/SUM(Exemplar)),0), ' %') AS ''

FROM 
(SELECT homebranch,ccode,location,COUNT(*) AS Exemplar, 0 AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location

UNION 

SELECT homebranch,ccode,location,0 AS Exemplar, COUNT(*) AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND issues='0'
GROUP BY homebranch,ccode,location

UNION

SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, COUNT(*) AS Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND items.datelastborrowed >= curdate() - interval 3 year 
GROUP BY homebranch,ccode,location

UNION 

SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, 0 Utlyear, COUNT(*) AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND onloan IS NOT NULL
GROUP BY homebranch,ccode,location) t

LEFT JOIN branches ON (t.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)

GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location
Hämtad från "https://koha.se/wiki/index.php?title=Nyckeltal_för_samlingarna_Bibliotek_Mellansjö&oldid=986"