Have Geek Brain

Count (*) in subquery

I need the number of items in an order.

In this example, order 10 has 0 and order 11 has 2 rows.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U'))
DROP TABLE OrderDetail
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderHeader') AND type in (N'U'))
DROP Table OrderHeader
GO
CREATE TABLE OrderHeader(
OrderHeaderID Int Primary Key Identity(10,1),
CustID Int)
GO
CREATE TABLE OrderDetail(
OrderDetailID Int Primary Key Identity(100,1),
OrderHeaderID Int REFERENCES OrderHeader,
ItemID Int)
GO
DECLARE @OrderHeaderID Int
INSERT INTO OrderHeader(CustID) VALUES(1000)
INSERT INTO OrderHeader(CustID) VALUES(1001)
SET @OrderHeaderID=SCOPE_IDENTITY()
INSERT INTO OrderDetail(OrderHeaderID,ItemID) VALUES(@OrderHeaderID,3)
INSERT INTO OrderDetail(OrderHeaderID,ItemID) VALUES(@OrderHeaderID,4)
GO
SELECT 
OrderDetailID,ItemID,OrderHeader.OrderHeaderID,CustID
FROM OrderDetail
JOIN OrderHeader
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
--

I need help here I need COUNT (*)

FROM OrderDetail WHERE OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
SELECT OrderHeader.OrderHeaderID,CustID,CountOfOrderDetail
FROM OrderHeader
JOIN OrderDetail
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
+1


a source to share


4 answers 


select 
   ord.OrderHeaderID
   , count(det.ItemID)
from
   OrderHeader ord
left outer join
   OrderDetail det
on
   det.OrderHeaderID = ord.OrderHeaderID
group by
   ord.OrderHeaderID


+4


a source 


SELECT OrderHeader.ID, count(OrderDetail.ID)
FROM OrderHeader
LEFT JOIN OrderDetail
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
GROUP BY OrderHeader.ID


+1


a source


Like this?

SELECT 
  h.OrderHeaderID,
  h.CustID,
  COUNT(d.OrderHeaderID) CountOfOrderDetail
FROM 
  OrderHeader h
  LEFT JOIN OrderDetail d ON d.OrderHeaderID = h.OrderHeaderID
GROUP BY
  h.OrderHeaderID,
  h.CustID
+1


a source


For future reference, you should not use the following:

SELECT COUNT(*)
FROM OrderDetail
GROUP BY OrderHeaderID

At first glance, it seems simple and elegant, but note that you will skip any order from the OrderHeader table that has no orders. If it is important that they are presented (with 0 for counting them) see Tomalak or Autocracy's answer here and here .

Thanks to KM for pointing this out! (see comments below)

0


a source






More articles:


All Articles