CODEWITHSUNDEEP
mysqlsqlselectinner-joinfrequency
Yaz
Yaz

Reputation: 55

Calculating frequency in MySQL

I am creating a library database and have four tables as follows;

I have been researching ways to work out the frequency in MySQL but after such as long time and misunderstanding I've decided to try get an example of how to work out the frequency on tables that I'll understand. Below are the four tables I am currently using.

I am looking to workout the loan frequency of every book that has been loaned 2 or more times. By doing this I am able to see how working out frequency would work when selecting specific values instead of all values.

From looking at my tables I would have to select the 'code' from the loan table, select all values that occur twice or more and then workout the frequency of the occurrence.

From my research I would decide to use an INNER JOIN to connect the tables, COUNT to count the number of values, GROUP BY to group the values and HAVING as WHERE may not be used. I am having trouble writing the query and continuously stumble upon errors. Could anyone use the example above to explain how they worked out the frequency of each book loaned two times or more? Thanks in advance

Table 1 - book

isbn title author 111-2-33-444444-5 Pro JavaFX Dave Smith 222-3-44-555555-6 Oracle Systems Kate Roberts 333-4-55-666666-7 Expert jQuery Mike Smith

Table 2 - copy

code isbn duration 1011 111-2-33-444444-5 21 1012 111-2-33-444444-5 14 1013 111-2-33-444444-5 7 2011 222-3-44-555555-6 21 3011 333-4-55-666666-7 7 3012 333-4-55-666666-7 14

Table 3 - student

no name school embargo 2001 Mike CMP No 2002 Andy CMP Yes 2003 Sarah ENG No 2004 Karen ENG Yes 2005 Lucy BUE No

Table 4 - loan

code no taken due return 1011 2002 2015.01.10 2015.01.31 2015.01.31 1011 2002 2015.02.05 2015.02.26 2015.02.23 1011 2003 2015.05.10 2015.05.31 1013 2003 2014.03.02 2014.03.16 2014.03.10 1013 2002 2014.08.02 2014.08.16 2014.08.16 2011 2004 2013.02.01 2013.02.22 2013.02.20 3011 2002 2015.07.03 2015.07.10 3011 2005 2014.10.10 2014.10.17 2014.10.20

Upvotes: 1

Views: 1068

Answers (1)

Andomar
Andomar

Reputation: 238088

You didn't specify the type of frequency, but this query calculates the number of loans per week for each book that was loaned more than once in 2014:

select  b.isbn
,       b.title
,       count(*) / 52 -- loans/week
from    loan l
join    copy c
on      c.code = l.code
join    book b
on      b.isbn = c.isbn
where   '2014-01-01' <= taken and taken < '2015-01-01'
group by
        b.isbn
,       b.title
having  count(*) > 1 -- loaned more than once

Upvotes: 1

Related Questions