SQL for OPAC statistics (using z69 OPAC-event table)

Description

How can we get a report of the number of Aleph OPAC searches and search types per college for the fiscal year? (We're a consortium.)  The " Canned Aleph OPAC statistics reports " article has some interesting suggestions, but we just need something basic.

Resolution

Note:  If you have the ARC (Aleph Reporting Center) product, then it has canned reports similar to the below.  You should check those first.

> s+ xxx01  <to connect to bib library>

To get the total number of searches of all kinds (between July 1, 2015, and June 30, 2016):

SQL> select count(*) from z69 where substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630';

To group results by base/location:

SQL> select Z69_SEARCH_BASE, count(*) from z69 where substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630' group by Z69_SEARCH_BASE;

To get counts of keyword searches by search types:

SQL> select count(*) from z69 where z69_search_query like 'Words=%' and substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630'; 
 <the above is for the WRD / W-001 All fields index>

Then do these for Words-in-Titles and Words-in-Authors, and Words-in-Subjects:

SQL> select count(*) from z69 where z69_search_query like '%Titles=%' and substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630';

SQL> select count(*) from z69 where z69_search_query like '%Authors=%' and substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630';

SQL> select count(*) from z69 where z69_search_query like '%Subjects=%' and substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630';

Then, using the tab00.eng column 11 values, do the same for whatever other W-nnn entries you want to report on.
 {The problem with having a single search giving counts for all Word searches is that the Word search type text (the part before the equal sign) is of varying length.}

To group browses by browse types:
SQL> select z69_scan_code, count(*) from z69 where z69_scan_code is not null and substr (z69_time_stamp,1,8) >= '20150701' and substr (z69_time_stamp,1,8) <= '20160630' group by z69_scan_code;

• Article last edited: 5-May-2017