时间戳

首页 > 解决方案 > SQL - 两个表之间的不同计数

sql - SQL - 两个表之间的不同计数

问题描述

我对我认为是一个相对简单的脚本有一个误会。希望我想太多逻辑了。

我要做的是对正确连接的不同列执行两次计数。

我想要的是：

    count(a.book_id) as count_of_books
    count(b.book_ref_number) as count_of_losses

预期产出

--------------------------------------------------------
| Book          | count_of_books      | count of losses|
--------------------------------------------------------
|Hunger Games   | 76                  | 31             |
--------------------------------------------------------
|Hop on Pop     | 27                  | 6              |
--------------------------------------------------------
|Pout Pout Fish | 138                 | 43             |
--------------------------------------------------------

在此处输入图像描述

我尝试了几个不同的脚本。这是我尝试过的两个脚本。

   (select count(*) from Inventory_Table x  ) Count1,
   (select count(*) from Loss_table b  ) Count2
   from Inventory_Table x
   right join Loss_table b on b.book_ref_number = x.book_id
   where rownum < 20



   select
   a.book_name,
   count(distinct a.book_id),
   count(b.book_ref_number)
   from Inventory_Table x
   right join Loss_table b  on trim(b.book_ref_number) = trim(a.book_id)

我得到的结果

--------------------------------------------------------
| Book          | count_of_books      | count of losses|
--------------------------------------------------------
|Moby Dick      | 4376                | 2574           |
--------------------------------------------------------

我正在为我的疏忽错误寻找指导。先感谢您

标签： sql

解决方案

0

SELECT book_name,COUNT(book_id),COUNT(book_ref_id) FROM Inventory_Table right join Loss_table on book_ref_number = book_id GROUP BY book_name

推荐阅读