Using Count to compile for each unique CustomerID

1

I want to create a column whereas I will count each instance a customerid has an orderid AND does not have an [ordershipped] . Below is sample DDL:

TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Company varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);

TABLE dbo.customers --cus
(
customerID (pk,varchar(5), NOT NULL),
firstname nvarchar(50) null,
lastname nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (customerID ASC)
);
TABLE dbo.orderdetails --ode
(
odeID smallint NOT NULL,
OrderID (pk, int, NOT NULL),
datebegin datetime NULL
CONSTRAINT PK_odeID PRIMARY KEY CLUSTERED (odeID ASC)
);

Will want to count the amount of orders per customerid (they can have more than one orderid for each customerid)

SELECT cu.customerid
,cu.company
,[still pending] = (select count(OrderID) from

orders o where cu.customerid = o.orderid and o.ordershipped = '')
FROM customers cu
LEFT JOIN orders o on cu.customerid = o.customerid
JOIN orderdetails ode on o.orderid = ode.orderid

GROUP BY cu.customerid, cu.company

... but I need to make sure each unique customerid appears on each row, with the number of [still pending] for each customerid
???
Thanks

2

This perhaps?

SELECT cu.customerid
	,cu.company
	,[still pending] = SUM(CASE WHEN o.ordershipped = '' THEN 1 ELSE 0 END)
FROM customers cu 
LEFT JOIN orders o on cu.customerid = o.customerid
-- Not required? JOIN orderdetails ode on o.orderid = ode.orderid
GROUP BY cu.customerid, cu.company
1 Like
3

Yes, this works in a slightly revamped version of what I am attempting to do, thanks!