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

[Amy Terlaga]

Thanks, Dan, that did the trick.

I modified yours by changing circ_lib to owning_lib.  Owning_lib was in the
copy_location table, not the copy table.

SELECT acl.name, COUNT(ac.barcode)
  FROM asset.copy_location acl 
    INNER JOIN asset.copy ac ON ac.location = acl.id
  WHERE acl.owning_lib = 4
    AND ac.deleted IS FALSE
  GROUP BY acl.name
  ORDER BY acl.name

Both seemed to yield the same results, so what's the difference when it
comes to item count?

Amy

=======================
Amy Terlaga
Assistant Director, User Services
Bibliomation
32 Crest Road
Middlebury, CT  06762
(203)577-4070 x101
http://www.biblio.org
------------------------------

Message: 2
Date: Thu, 10 Dec 2009 11:50:22 -0500
From: Dan Scott <dan at coffeecode.net>
Subject: Re: [OPEN-ILS-GENERAL] SQL Help - Item Count by
	Location/Collection
To: Evergreen Discussion Group
	<open-ils-general at list.georgialibraries.org>
Message-ID: <1260463822.2579.8.camel at dan-karmic-liblap>
Content-Type: text/plain; charset="UTF-8"

On Thu, 2009-12-10 at 11:34 -0500, Amy Terlaga wrote:
> 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.


If you want just a location/item count for a given library, the
following seems to work (substitute your org_unit ID for '103' below) -
although note that I'm using circ_lib rather than owning_lib, so you
might need to tweak somewhat:

SELECT acl.name, COUNT(ac.barcode) 
  FROM asset.copy_location acl 
    INNER JOIN asset.copy ac ON ac.location = acl.id 
  WHERE ac.circ_lib = 103 
    AND ac.deleted IS FALSE 
  GROUP BY acl.name 
  ORDER BY acl.name
;
                          name                           | count  
---------------------------------------------------------+--------
ARTG-COLL                                               |      3
Archives (1st floor)                                    |   3230
Archives (1st floor) - Faculty Authors                  |    798
Archives (1st floor) - Rare Books                       |   4654
Archives (1st floor) - Reference                        |    310
Archives (1st floor) - Regional Collection              |   2403
Archives (1st floor) - Theses and essays                |   1278
Archives (1st floor) - Vertical files                   |    246
Atlases (2nd floor)                                     |    507
...

For a location/item count for all libraries in your system, the
following works (again, circ_lib instead of owning_lib):

SELECT aou.shortname, SUBSTR(acl.name, 0, 20), COUNT(ac.barcode) 
  FROM asset.copy_location acl 
    INNER JOIN asset.copy ac ON ac.location = acl.id 
    INNER JOIN actor.org_unit aou ON ac.circ_lib = aou.id 
  WHERE ac.deleted IS FALSE 
  GROUP BY aou.shortname, acl.name 
  ORDER BY aou.shortname, acl.name
;

 OSTMA      | Stacks              |    122
 OSTMA      | UNKNOWN             |      3
 OSUL       | ARTG-COLL           |      3
 OSUL       | Archives (1st floor |   3230
 OSUL       | Archives (1st floor |    798
 OSUL       | Archives (1st floor |   4654
 OSUL       | Archives (1st floor |    310
...

Dan






End of Open-ils-general Digest, Vol 42, Issue 18
************************************************