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

[Dan Scott]

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