count - 内连接 + 计数 + 在 Psequel 中的位置

Question

新学员来了。

我正在处理两张表：一张是出版商数据，一张是图书数据。

我对特定国家（例如美国兰登书屋）的特定出版商在INNER JOINwithWHERE子句中出版的标题感兴趣。到目前为止，一切都很好：

SELECT 
book_infotable.tblbook_title, 
tblpublisher_publisher,
tblpublisher_country 
FROM book_infotable
INNER JOIN pub_infotable 
ON book_infotable.pubid = pub_infotable.pubid
WHERE tblpublisher_publisher = 'Oxford University Press' AND 
tblpublisher_country = 'GB';

我想补充COUNT一点，以计算特定出版商办公室出版的书名数量。

目标表

但是，当我尝试以下操作时COUNT，

SELECT COUNT(tblbook_title) 
AS title_count, pub_infotable.tblpublisher_publisher, tblpublisher_country 
FROM book_infotable 
INNER JOIN pub_infotable 
ON book_infotable.pubid = pub_infotable.pubid 
WHERE tblpublisher_publisher = 'Knopf' AND tblpublisher_country = 'GB'
GROUP BY pub_infotable.tblpublisher_publisher, tblpublisher_country, 
book_infotable.tblbook_title;

我结束了

计数列中具有持久“1”的表

任何帮助深表感谢！谢谢！

标签： countinner-joinwhere-clausepsql

Answer 1

尝试执行此查询

SELECT COUNT(1) AS title_count, tblpublisher_publisher, tblpublisher_country 
from (
SELECT pub_infotable.tblpublisher_publisher AS tblpublisher_publisher, 
tblpublisher_country 
FROM book_infotable 
INNER JOIN pub_infotable 
ON book_infotable.pubid = pub_infotable.pubid 
WHERE tblpublisher_publisher = 'Oxford University Press' AND 
tblpublisher_country = 'GB'
GROUP BY book_infotable.tblpublisher_publisher, tblpublisher_country, 
book_infotable.tblbook_title ) temp 
GROUP BY tblpublisher_publisher, tblpublisher_country;