Consider these Tables:
Table Items:
ItemID ItemName
------------------
1 N1
2 N2
3 N4
4 N5
and in MyTbl table I have a ItemID that may be like this:
ItemId
----
1
1
3
4
4
4
I want to write a query that return this result:
ItemId count
-------------------
1 2
2 0
3 1
4 3
How I can do this without Cursors?
You can select every item from Items
and LEFT JOIN
MyTbl
on the common ItemID
, counting the matches;
select
Items.itemId,
count(MyTbl.itemId) as count
from Items
left join MyTbl on (MyTbl.ItemID = Items.ItemID)
group by Items.itemId
order by Items.itemId
Try this:
declare @Items as table(ItemID int,ItemName varchar(20))
declare @MyTbl as table(ItemId int)
insert into @Items values(1, 'N1')
insert into @Items values(2, 'N2')
insert into @Items values(3, 'N4')
insert into @Items values(4, 'N5')
insert into @MyTbl values(1)
insert into @MyTbl values(1)
insert into @MyTbl values(3)
insert into @MyTbl values(4)
insert into @MyTbl values(4)
insert into @MyTbl values(4)
select I.ItemID, COUNT(M.ItemId) as [COUNT] from @Items I left outer join @MyTbl M on I.ItemID = M.ItemId
group by I.ItemID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With