Select rows based on count of other rows

Question

Consider this query:

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

How do I only show the rows with a Ratio > 1?

I've tried using a local variable and adding this:

WHERE @ratio > 1

But that just kills my server (runs forever until it crashes).

add: HAVING COUNT(DISTINCT i.itemnumber) / COUNT(o.itemnumber) > 1 , after GROUP BY — Mitch Wheat, Feb 15 '18 at 23:08
@Barmar I added that and my server still crashes. There's nothing wrong with the server as removing that statement returns the results. `SELECT b.biblionumber, b.title, COUNT(DISTINCT i.itemnumber) AS 'Total Copies', COUNT(o.itemnumber) AS 'Total Issues', @ratio := (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 HAVING @ratio > 1` — BoostBoy, Feb 15 '18 at 23:16
And use backticks around the aliases, not single quotes. See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Feb 15 '18 at 23:19
@Barmar I've put a LIMIT to return only 100 results. I also tried removing the ratio from the select statement and adding this instead: `HAVING (COUNT(DISTINCT i.itemnumber) / COUNT(o.itemnumber)) > 1` — BoostBoy, Feb 15 '18 at 23:25
Okay that did eventually return results but it was extremely slow. The problem could be that it is dividing by 0 for some of the rows — BoostBoy, Feb 15 '18 at 23:31

score 0 · Answer 1 · answered Feb 16 '18 at 01:00

Performance problems can often be due to aggregating after joining, because the joins produce a very large intermediate table. It may be better to group your tables before joining them.

SELECT b.biblionumber, b.title, 
    SUM(i.count) AS `Total Copies`, SUM(o.count) AS `Total Issues`, 
    SUM(i.count)/SUM(o.count) AS Ratio
FROM biblio AS b
INNER JOIN (
    SELECT biblionumber, itemnumber, COUNT(*) AS count
    FROM items
    GROUP BY biblionumber, itemnumber) AS i ON b.biblionumber = i.biblionumber
INNER JOIN (
    SELECT itemnumber, COUNT(*) AS count
    FROM old_issues
    GROUP BY itemnumber) AS o ON i.itemnumber = o.itemnumber
GROUP BY b.biblionumber, b.title
HAVING Ratio > 1

I've also changed it from LEFT JOIN to INNER JOIN. In order to get a positive ratio, you need matches in both tables, so there's no need to allow non-matching rows with an outer join. Inner joins tend to perform better in MySQL, so always prefer them when possible.

It's also possible you can address your original problem just by switching to INNER JOIN.

Select rows based on count of other rows

1 Answers1