Using COUNT of query in results

Question

I have 3 tables:

biblio (biblionumber, title);
items (itemnumber, biblionumber);
old_issues (issues_id, itemnumber, issuedate)

biblio has a one to many relationship to items.
items has a one to many relationship to old_issues.

I want to know how many items are in the biblio table per unique biblio title, and how many times the item has been issued within a date range. Then in the last column I want the ratio of items versus the number of times it's been issued:

biblio title | Copies of title (Count of items in biblio) | Count of issues | count of items / count of issues

The problem I'm having is that if I join all the tables, I won't get an accurate count of how many items are in the biblio table as they will only be counted if they have been issued.

For example, if I have one title "Terminator 2" (one unique entry in the biblio table) and it has 5 copies (5 unique entries in the items table), but none of these copies have been issued before, then I will get a count of 0 items.

I should end up with a result like:

"Terminator 2" has 5 copies in the library, and has been issued 245 times in the year 2017. That is a ratio of 0.02 meaning we have to order more copies to reach demand!

I'm thinking I have to do a subquery/nested query of some kind that is independent of the results in the other table, but somehow join the table together to get one view. I'm too amateur to know how to do that, so any help would be appreciated. Sorry if my explanation is hard to understand.

Here's the query I'm trying to work with:

SELECT biblio.title, COUNT(items.itemnumber), COUNT(old_issues.itemnumber)
FROM old_issues
JOIN items ON old_issues.itemnumber = items.itemnumber
JOIN biblio ON items.biblionumber = biblio.biblionumber
WHERE old_issues.issuedate BETWEEN '2018-02-01' AND '2018-02-03'
GROUP BY biblio.biblionumber

You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh
– jarlh, Commented Feb 15, 2018 at 7:34
Notice how much more succinct is John's answer, as compared with your question — Strawberry
– Strawberry, Commented Feb 15, 2018 at 7:59
Thanks for the tip. This is literally my first post, and I kind of did what was suggested in your link, albeit in not such a succint way as you put it. I'll keep that in mind for future posts. Thanks — BoostBoy
– BoostBoy, Commented Feb 15, 2018 at 8:03

John Woo · Accepted Answer · 2018-02-15 06:36:44Z

3

You might want to interchange the tables and use LEFT JOIN so rows will still show even if not within the date range but with values of 0.

SELECT  b.biblionumber,
        b.title, 
        COUNT(DISTINCT i.itemnumber) AS `Total Copies`, 
        COUNT(o.itemnumber) AS `Total Issues`,
        COUNT(DISTINCT  i.itemnumber) / COUNT(o.itemnumber)   AS `Ratio`
FROM    biblio b
        LEFT JOIN items i
            ON b.biblionumber = i.biblionumber
        LEFT JOIN old_issues o
            ON o.itemnumber = i.itemnumber
                AND o.issuedate BETWEEN '2018-02-01' AND '2018-02-03'
GROUP   BY  b.biblionumber,
            b.title

Here's a Demo.

edited Feb 15, 2018 at 6:36

answered Feb 15, 2018 at 6:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BoostBoy Over a year ago

Great! After looking at your demo, this looks like it could work. I didn't realise you could put extra criteria in the join statement and also I thought my join statement would keep all data from both tables, but looks like left join is what I was after. I'll give this a try tomorrow.

John Woo Over a year ago

@BoostBoy on the other hand, if you want to filter out the result based on the date range, change LEFT to INNER and move the date filter in the WHERE clause.

Collectives™ on Stack Overflow

Using COUNT of query in results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related