sql reports in koha

MySQL Reports in Koha

MySQL Reports in Koha

Nicole C. EngardKoha Documentation Manager

KohaCon 2009


KohaCon 2009

MySQL IntroMySQL Intro

MySQL is the language used to query data in the Koha database.

Koha’s database structure can be viewed here:

http://tinyurl.com/kohadbstructure

MySQL’s Documentation Library is a great source for help and information

http://dev.mysql.com/doc/index.html

MySQL is the language used to query data in the Koha database.

Koha’s database structure can be viewed here:


MySQL’s Documentation Library is a great source for help and information






Reading DB StructureReading DB Structure

CREATE TABLE `branches` ( `branchcode` varchar(10) NOT NULL default '', `branchname` mediumtext NOT NULL, `branchaddress1` mediumtext, `branchaddress2` mediumtext, `branchaddress3` mediumtext, `branchphone` mediumtext, `branchfax` mediumtext, `branchemail` mediumtext, `issuing` tinyint(4) default NULL, `branchip` varchar(15) default NULL, `branchprinter` varchar(100) default NULL, UNIQUE KEY `branchcode` (`branchcode`) )

CREATE TABLE `branches` ( `branchcode` varchar(10) NOT NULL default '', `branchname` mediumtext NOT NULL, `branchaddress1` mediumtext, `branchaddress2` mediumtext, `branchaddress3` mediumtext, `branchphone` mediumtext, `branchfax` mediumtext, `branchemail` mediumtext, `issuing` tinyint(4) default NULL, `branchip` varchar(15) default NULL, `branchprinter` varchar(100) default NULL, UNIQUE KEY `branchcode` (`branchcode`) )

Reading DB StructureReading DB Structure

This table’s name is ‘branches’ and it stores the information about libraries or branches in Koha.

Each field is easy to identify because of its name (ex. branchname is the library name).

A field with a number in parens after it is a field that is limited in size.

So varchar(10) means the field can have no more than 10 characters in it

Lastly, we see that ‘branchcode’ is the unique key or unique identifier in the table.

This table’s name is ‘branches’ and it stores the information about libraries or branches in Koha.

Each field is easy to identify because of its name (ex. branchname is the library name).

A field with a number in parens after it is a field that is limited in size.

So varchar(10) means the field can have no more than 10 characters in it

Lastly, we see that ‘branchcode’ is the unique key or unique identifier in the table.

Reports in KohaReports in Koha

Koha has a very powerful guided reports wizard, but sometimes you want to write your own report - for that you need SQL

To start learning how to write SQL queries, you may want to create some guided reports and view the outputted SQL

Koha has a very powerful guided reports wizard, but sometimes you want to write your own report - for that you need SQL

To start learning how to write SQL queries, you may want to create some guided reports and view the outputted SQL

Querying SQLQuerying SQL

Once you know your database structure, querying SQL is very straightforward.

For Example:

SELECT branchcode,branchname FROM branches ORDER BY branchname ASC

This will list all of the branches and their codes in alphabetical order by branch name

Once you know your database structure, querying SQL is very straightforward.

For Example:

SELECT branchcode,branchname FROM branches ORDER BY branchname ASC

This will list all of the branches and their codes in alphabetical order by branch name

Querying a Single TableQuerying a Single Table

To query a single table you will structure your query like this:

SELECT column_names from table_name [WHERE ...conditions] [ORDER BY ...conditions];

Statements in brackets are optional

You can also select everything in a table by using an * in place of column_names

To query a single table you will structure your query like this:

SELECT column_names from table_name [WHERE ...conditions] [ORDER BY ...conditions];

Statements in brackets are optional

You can also select everything in a table by using an * in place of column_names

Querying MultipleTables

Querying MultipleTables

To query multiple tables you will have to join them on a key - what we saw as a UNIQUE KEY in the structure

SELECT a.column_names, b.column_names FROM a LEFT JOIN b ON (a.key=b.key);

OR

SELECT branches.branchname, items.barcode, COUNT(issues.issuedate) as totalissues FROM branches LEFT JOIN issues ON (issues.branchcode = branches.branchcode) LEFT JOIN items ON (issues.itemnumber = items.itemnumber) GROUP BY items.barcode;

To query multiple tables you will have to join them on a key - what we saw as a UNIQUE KEY in the structure

SELECT a.column_names, b.column_names FROM a LEFT JOIN b ON (a.key=b.key);

OR

SELECT branches.branchname, items.barcode, COUNT(issues.issuedate) as totalissues FROM branches LEFT JOIN issues ON (issues.branchcode = branches.branchcode) LEFT JOIN items ON (issues.itemnumber = items.itemnumber) GROUP BY items.barcode;

Common FunctionsCommon Functions

COUNT(FIELD) or SUM(FIELD)

Counts the number of or adds up the total value of results in a column

CURDATE()

Is the current date (not time, just date)

MONTH(FIELD) and YEAR(FIELD)

Return the month and year from a field

DATE_SUB(DATE, INTERVAL)

Subtract a period of time from a date

COUNT(FIELD) or SUM(FIELD)

Counts the number of or adds up the total value of results in a column

CURDATE()

Is the current date (not time, just date)

MONTH(FIELD) and YEAR(FIELD)

Return the month and year from a field

DATE_SUB(DATE, INTERVAL)

Subtract a period of time from a date

Common WHERE clauses

Common WHERE clauses

WHERE DATE BETWEEN ‘2009-04-01’ AND ‘2009-05-01’

Searches between dates (if no time present it assumes 00:00)

WHERE accounttype IN("A","F","FU")

The same as saying WHERE accounttype = “A” or accounttype = “F” ... etc

Can also do NOT IN( ) for !=

WHERE DATE BETWEEN ‘2009-04-01’ AND ‘2009-05-01’

Searches between dates (if no time present it assumes 00:00)

WHERE accounttype IN("A","F","FU")

The same as saying WHERE accounttype = “A” or accounttype = “F” ... etc

Can also do NOT IN( ) for !=

Date & Time HelpDate & Time Help

The most common use for reports is for end of the month or end of the year statistics

The MySQL manual on Date & Time functions is essential for these queries

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

The most common use for reports is for end of the month or end of the year statistics

The MySQL manual on Date & Time functions is essential for these queries








Let’s PlayLet’s Play

Think about the reports you would like to see in your Koha system and we’ll try to write at least one report for each of you.

Think about the reports you would like to see in your Koha system and we’ll try to write at least one report for each of you.

Reports by NicoleReports by Nicole

Patrons with fines:

SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,FORMAT(SUM(accountlines.amountoutstanding),2) as due FROM borrowers LEFT JOIN accountlines on (borrowers.borrowernumber=accountlines.borrowernumber) WHERE accountlines.amountoutstanding > 0 GROUP BY borrowers.cardnumber ORDER BY borrowers.surname asc

Patrons with fines:

SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,FORMAT(SUM(accountlines.amountoutstanding),2) as due FROM borrowers LEFT JOIN accountlines on (borrowers.borrowernumber=accountlines.borrowernumber) WHERE accountlines.amountoutstanding > 0 GROUP BY borrowers.cardnumber ORDER BY borrowers.surname asc


Long Overdues (12 days):

SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,items.barcode,issues.date_due FROM borrowers LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 12 DAY) AND issues.returndate is NULL ORDER BY borrowers.surname asc

Long Overdues (12 days):

SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,items.barcode,issues.date_due FROM borrowers LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 12 DAY) AND issues.returndate is NULL ORDER BY borrowers.surname asc


Total number of items circulated from a branch other than the owning branch

SELECT count(*) as total from statistics LEFT JOIN items on (statistics.itemnumber = items.itemnumber) where statistics.branch != items.homebranch AND statistics.datetime between '2008-01-01' and '2008-12-31'

Total number of items circulated from a branch other than the owning branch

SELECT count(*) as total from statistics LEFT JOIN items on (statistics.itemnumber = items.itemnumber) where statistics.branch != items.homebranch AND statistics.datetime between '2008-01-01' and '2008-12-31'


How many new children's (shelving locations) books (Item types) did Library X add to the shared catalog in November 2008?

SELECT count(i.biblionumber) as added, i.itype, i.homebranch, i.location from items i WHERE YEAR(i.dateaccessioned) = 2008 AND MONTH(i.dateaccessioned) = 10 AND i.itype = 'BOOK' AND i.location = 'CHILDRENS' GROUP BY i.homebranch ORDER BY i.location asc

How many new children's (shelving locations) books (Item types) did Library X add to the shared catalog in November 2008?

SELECT count(i.biblionumber) as added, i.itype, i.homebranch, i.location from items i WHERE YEAR(i.dateaccessioned) = 2008 AND MONTH(i.dateaccessioned) = 10 AND i.itype = 'BOOK' AND i.location = 'CHILDRENS' GROUP BY i.homebranch ORDER BY i.location asc

Other ReportsOther Reports

Number of titles cataloged (added) between and / or since x date(s):

SELECT COUNT(*) FROM biblio WHERE datecreated BETWEEN '2009-01-01' and '2009-02-01'

SELECT COUNT(*) FROM biblio WHERE datecreated > '2009-02-01'

Number of titles cataloged (added) between and / or since x date(s):

SELECT COUNT(*) FROM biblio WHERE datecreated BETWEEN '2009-01-01' and '2009-02-01'

SELECT COUNT(*) FROM biblio WHERE datecreated > '2009-02-01'

More HelpMore Help

NExpress ROCKS!

Reports: www.nexpresslibrary.org/training/reports-training/

Training Videos: www.nexpresslibrary.org/category/tutorial/

Several report videos

NExpress ROCKS!

Reports: www.nexpresslibrary.org/training/reports-training/

Training Videos: www.nexpresslibrary.org/category/tutorial/

Several report videos

http://www.nexpresslibrary.org/training/reports-training/




Thank YouThank You


KohaCon 2009


KohaCon 2009

Slides Available Onlineon Koha Conference WikiSlides Available Online

on Koha Conference Wiki

sql reports in koha

Technology

branchcode left join

join issues

koha koha

borrowernumber left

branches order

field date

koha nicole

null order