select t.barcode,count(*) from items i,transactions t where t.barcode = i.barcode group by t.barcode limit 100;
this gets all the barcodes with a count of the number of times they've been checked out.
select deweyclass,itemtype,ckoutDateTime,ckinDateTime from items i, transactions t where i.barcode = t.barcode and deweyclass is not null and itemtype like '%bk' limit 100000;
this gets all check-out transactions by deweyclass.
-- karl
SQL queries on the vislab2 db
Re: SQL queries on the vislab2 db
select itemtype,count(*) c from items i group by itemtype order by c desc;
count the number of items for each itemtype and sort in descending order.
count the number of items for each itemtype and sort in descending order.
Re: SQL queries on the vislab2 db
select deweyclass,count(*) c from transactions t,items i where t.barcode = i.barcode and deweyclass like '70%' group by deweyclass order by c desc;
count the number of times each deweyclass that starts with '70' has been checked out.
count the number of times each deweyclass that starts with '70' has been checked out.
Re: SQL queries on the vislab2 db
here's a query makes a sorted list of each deweyclass, counts the total number of checkout transactions for each deweyclass and makes a list of all the barcodes of the items that belong to each deweyclass.
select deweyclass as deweyClass, count(*) as numberOfCheckouts, group_concat(distinct i.barcode) as listOfBarcodes from transactions t,items i where t.barcode = i.barcode and deweyclass is not null group by deweyclass order by deweyclass
this query finishes in about a minute and a half:
Query OK, 46132 rows affected, 2727 warnings (1 min 22.27 sec)
the data looks like this:
999.23 44 0010044302999,0010004385752,0010044303039
998.20043 121 0010045316790,0010045316816,0010045316808,0010045316824
-- karl
select deweyclass as deweyClass, count(*) as numberOfCheckouts, group_concat(distinct i.barcode) as listOfBarcodes from transactions t,items i where t.barcode = i.barcode and deweyclass is not null group by deweyclass order by deweyclass
this query finishes in about a minute and a half:
Query OK, 46132 rows affected, 2727 warnings (1 min 22.27 sec)
the data looks like this:
999.23 44 0010044302999,0010004385752,0010044303039
998.20043 121 0010045316790,0010045316816,0010045316808,0010045316824
-- karl