[OPEN-ILS-GENERAL] SQL Help - Item Count by Location/Collection

[Amy Terlaga]

Hi all-

 

I'm just getting started in trying to write some basic SQL queries that will
work in the Evergreen system.

 

One I'm working on now that I haven't succeeded at is an Item Count by
Location/Collection report.

 

What I've done - modified a working Item Count by Library report.

 

Here's the original (works):

 

SELECT aou.name, count(ac.barcode)

FROM actor.org_unit aou

INNER JOIN asset.copy ac

ON aou.id = ac.circ_lib

GROUP BY aou.name

ORDER BY 2 DESC;

 

 

Here's my modified report (runs but gives inaccurate results):

 

SELECT aou.name, acl.name, count(ac.location)

FROM actor.org_unit aou, asset.copy_location acl, asset.copy ac

WHERE aou.id = ac.circ_lib

AND ac.circ_lib = acl.owning_lib

GROUP BY aou.name, acl.name

ORDER BY 2 DESC;

 

Could somebody give me some guidance as to what I need to change to get this
to run with correct results?

The count is obviously wrong - I've changed what's in the parentheses a few
times, and each time it yields the same results, which I find very strange.

 

Over my head in CT,

Amy

 

=======================

Amy Terlaga

Assistant Director, User Services

Bibliomation

32 Crest Road

Middlebury, CT  06762

(203)577-4070 x101

http://www.biblio.org

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20091210/699c947c/attachment.htm