SQL queries on the vislab2 db

Post Reply
yerkes
Posts: 6
Joined: Tue Jan 18, 2011 9:23 am

SQL queries on the vislab2 db

Post by yerkes » Tue Feb 15, 2011 11:54 am

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

yerkes
Posts: 6
Joined: Tue Jan 18, 2011 9:23 am

Re: SQL queries on the vislab2 db

Post by yerkes » Tue Feb 15, 2011 12:36 pm

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.

yerkes
Posts: 6
Joined: Tue Jan 18, 2011 9:23 am

Re: SQL queries on the vislab2 db

Post by yerkes » Tue Feb 15, 2011 1:02 pm

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.

yerkes
Posts: 6
Joined: Tue Jan 18, 2011 9:23 am

Re: SQL queries on the vislab2 db

Post by yerkes » Tue Feb 22, 2011 2:36 pm

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

Post Reply