sql server - get count of items in two different tables into one resulting table

Question

I am trying to obtain two different counts in a query. One count would be the count of the specific item in one table, and the other count would be the same but from a different table. I can write 2 different queries that provides me the info but in two different tables. I would like to write one query that puts it all into one table. Thank you guys for any of suggestions.

EDIT: To clarify, I would like to add the count from the second query to a column on the first query.

My query

select d.description, count(item_id) from productdetails pd
join inventory i on i.itemnum=pd.item_id
join departments d on d.dept_id=i.dept_id
where i.last_sold is not null and in_stock !=0
group by d.description

select d.description, count(itemnum) 
from inventory i
join departments d on d.dept_id=i.dept_id
where in_stock != 0 and last_sold is not null
group by d.description

question from:https://stackoverflow.com/questions/66050966/get-count-of-items-in-two-different-tables-into-one-resulting-table

Answer 1

Place your queries in a sub-query where each count has been established and the "missing" count is assigned a value of zero.

Next, sum the count.

   SELECT smmry.description
     , SUM(smmry.pd_item_cnt) pd_item_cnt
     , SUM(smmry.itemnum_cnt) itemnum_cnt
  FROM (
        SELECT d.description
             , COUNT(pd.item_id) pd_item_cnt
             , 0 itemnum_cnt
          FROM productdetails pd
          JOIN inventory i
            ON i.itemnum =  pd.item_id
          JOIN departments d
            ON d.dept_id =  i.dept_id
         WHERE i.last_sold IS NOT NULL
           AND in_stock  != 0
         GROUP BY d.description
         UNION ALL
        SELECT d.description
             , 0 pd_item_cnt
             , COUNT(i.itemnum) itemnum_cnt
          FROM inventory i
          JOIN departments d
            ON d.dept_id =  i.dept_id
         WHERE in_stock  != 0
           AND last_sold IS NOT NULL
      GROUP BY d.description
       ) smmry
 GROUP BY smmry.description