How can count all item of query?-CodePudding

Structure of data:

Table1: ITEM_SPEC_ID, NAME, PRIMARY KEY (ITEM_SPEC_ID)
Table2: ITEM_SPEC_ID, TOOL_ID, PRIMARY KEY (ITEM_SPEC_ID, TOOL_ID)

I want get ITEM_SPEC_ID, TOOL_ID and count total number of TOOL_ID

This is my query, but it doesn't return the correct result:

SELECT DISTINCT   
    a.ITEM_SPEC_ID, b.TOOL_ID, COUNT(b.TOOL_ID) AS num_tool_id 
FROM   
    Table1 a  
JOIN 
    Table2 b ON a.ITEM_SPEC_ID = b.ITEM_SPEC_ID 
GROUP BY 
    a.ITEM_SPEC_ID, b.TOOL_ID

Expected result: num_tool_id = 1

Expected output: I want value of column num_tool_id = 2 in above case.

How can I count all items of query?

CodePudding user response：

Please make sure you share enough information to us. Else, it's hard for us to help! Adding a table for e

You can do in this way if you want to get the number of unique tool_id

Check out this db fiddle

SELECT a.item_spec_id, a.tool_id,
(SELECT COUNT(DISTINCT(tool_id)) as tot_num FROM items) as tot_num
FROM items a