SQL for how many different patrons borrowed items this year, etc.

• Article Type: General
• Product: Aleph
• Product Version: 20

Desired Outcome Goal:
SQL Commands for finding the following information:

1. How many different patrons borrowed items in 2011-12?
2. How many different patrons of each patron status borrowed items in 2011-12?
3. How many different freshmen, sophomores, juniors, and seniors borrowed items?

Procedure:
1. How many different patrons borrowed items in 2011-12:

abc50@ALEPH20> select count (unique z35_id) from z35 where z35_event_type like '5%' and Z35_EVENT_DATE >= '20110701' and Z35_EVENT_DATE <= '20120630';
**** Hit return to continue ****

COUNT(UNIQUEZ35_ID)
-------------------
1695


2. How many different patrons of each patron status borrowed items in 2011-12?

abc50@ALEPH20> select z35_bor_status, count (unique z35_id) from z35 where z35_event_type like '5%' and Z35_EVENT_DATE >= '20110701' and Z35_EVENT_DATE <= '20120630' group by z35_bor_status;
**** Hit return to continue ****

Z3 COUNT(UNIQUEZ35_ID)
-- -------------------
01 300
02 1322
03 21
04 9
05 7
06 36
07 1
08 2
09 1
10 4
11 1


3. How many different freshmen, sophomores, juniors, and seniors borrowed items?

abc50@ALEPH20> select z35_bor_type, count (unique z35_id) from z35 where z35_event_type like '5%' and Z35_EVENT_DATE >= '20110701' and Z35_EVENT_DATE < ='20120630' group by z35_bor_type;
**** Hit return to continue ****

Z3 COUNT(UNIQUEZ35_ID)
-- -------------------
FR 255
JR 440
SO 442
SR 510
UN 52
371

Note: the total of the last query is 2070 -- which is 375 more than the result of the first query.... Because there are students who were SO and JR in the same year, etc. based on the number of credits they have, rather than the year in school.


Use the following SQL to see each patron who has a z35 record for 2011-12:

select unique z35_id, z35_bor_status, z35_bor_type from z35 where z35_event_type like '5%' and Z35_EVENT_DATE >= '20110701' and Z35_EVENT_DATE <= '20120630' order by z35_id;

This may help confirm that the counts are correct.