Reputation: 21
I am trying to have my select query pull the count of items that exist in both tables in one column, and the count of the items that only exist in one table. These two queries give me the data I need, but would like it come from one query. Thanks in advance.
select
d.description,
count(pd.item_id)
from productdetails pd
join inventory i on i.itemnum=pd.item_id
inner join departments d on d.dept_id=i.dept_id
where i.last_sold is not null and i.in_stock !=0 and len(i.itemnum) >4 and d.DEPT_ID NOT IN ('1010','1010c','1010cc','1010h','1010h','1011','1012','1013','1014','1015','1016','1017','1018','1015p','1015d','1015s','1015u','1251','1401','2000')
group by d.description
select d.description,
count(pd.item_id)
from productdetails pd
join inventory i on i.itemnum=pd.item_id
inner join departments d on d.dept_id=i.dept_id
group by d.description
Desired Result:
Upvotes: 1
Views: 25
Reputation: 2063
You can combine a SUM and an IIF statements to achive your goal :
select d.description,
count(pd.item_id),
SUM(IIF(i.last_sold is not null and i.in_stock !=0 and len(i.itemnum) >4 and d.DEPT_ID NOT IN ('1010','1010c','1010cc','1010h','1010h','1011','1012','1013','1014','1015','1016','1017','1018','1015p','1015d','1015s','1015u','1251','1401','2000'), 1, 0))
from productdetails pd
join inventory i on i.itemnum=pd.item_id
inner join departments d on d.dept_id=i.dept_id
group by d.description
Upvotes: 1