英文:

Modify SQL to get count of all titles with the same biblionumber

问题 {#heading}

我正在尝试根据图书馆借阅次数最多的标题来获取信息，基于它们被借阅的次数。我正在使用的代码是从koha SQL报告库中采用的，但似乎没有提供标题的计数，而是提供了项目的计数。我想根据biblionumber获得所有标题的计数，就像我在下面的SQL代码中尝试的那样。

SELECT
    biblio.title,
    biblio.author,
    biblio.biblionumber,
    COUNT(biblio.biblionumber) AS "count"
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
WHERE
    (statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59')
    AND statistics.type = 'issue'
    AND items.itype IN ('BK', 'REF', 'TH', 'RES')
GROUP BY biblio.biblionumber, biblio.title, biblio.author 
ORDER BY COUNT(biblio.biblionumber) DESC LIMIT 10;

koha的数据库架构可以在这里找到：http://schema.koha-community.org/

如下图所示，biblionumber为83的标题应该出现为一条记录，总计数为11。 英文:

I am trying to get the most popular titles borrowed at a library based on the number of times it has been borrowed. The code I am using is adopted from koha SQL reports library but it doesn't seem to give a count of titles but instead gives a count of items. I would like to have a count of all titles based on the biblionumber as I have attempted in the SQL code shown below.

SELECT
	biblio.title,
	biblio.author,
	biblio.biblionumber,
	COUNT(biblio.biblionumber) AS "count"
FROM statistics
LEFT JOIN items ON (statistics.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
WHERE
	(statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59')
	AND statistics.type = 'issue'
    AND items.itype IN ('BK', 'REF', 'TH', 'RES')
GROUP BY statistics.itemnumber, biblio.title, biblio.author 
ORDER BY COUNT(biblio.biblionumber) DESC LIMIT 10; 

The database schema for koha is here http://schema.koha-community.org/

As shown in the figure below, the title whose biblionumber is 83 should appear as one record with a total count of 11.

答案1 {#1}

得分: 1

尝试以下内容：

SELECT
    biblio.title,
    biblio.author,
    biblio.biblionumber,
    COUNT(biblio.biblionumber) AS "count"
FROM statistics, items, biblio
WHERE statistics.itemnumber = items.itemnumber 
AND items.biblionumber = biblio.biblionumber    
AND statistics.type = 'issue'
AND items.itype IN ('BK', 'REF', 'TH', 'RES')
AND (statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59')
GROUP BY biblio.biblionumber, biblio.title, biblio.author 
ORDER BY COUNT(biblio.biblionumber) DESC LIMIT 10;

英文:

Try the following

SELECT
    biblio.title,
    biblio.author,
    biblio.biblionumber,
    COUNT(biblio.biblionumber) AS "count"
FROM statistics,items,biblio
WHERE statistics.itemnumber = items.itemnumber 
AND items.biblionumber = biblio.biblionumber    
AND statistics.type = 'issue'
AND items.itype IN ('BK', 'REF', 'TH', 'RES')
AND (statistics.datetime BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59')
GROUP BY biblio.biblionumber, biblio.title, biblio.author 
ORDER BY COUNT(biblio.biblionumber) DESC LIMIT 10;