Im needing to create a derived column that takes unique customerids and sums up the instances of certain statuses in an orders table (and eventually do a rollup grandtotal) :
TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Sourceid (pk uniqueidentifier NOT NULL),
Status varchar(50) null,
Ordershipped datetime NULL,
Orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
TABLE dbo.customers --cu
(
CustomerID (pk,varchar(5), NOT NULL),
Firstname nvarchar(50) null,
Lastname nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (CustomerID ASC)
);
SELECT coalesce (cu.customerid,'Total') AS CUSTID
,[HOLDING] = sum(select CASE WHEN o.status LIKE '%shipment date schedule%' OR o.status LIKE '%shipment ready%' THEN 1 ELSE 0 end from orders o left join customers cu on o.CustomerID = cu.CustomerID)
FROM CU.customers cu
LEFT JOIN
Orders o
---there will be other table joins, but right now i am isolating this particular segment
ON cu.customerid = o.customerid
GROUP BY ROLLUP(cu.customerid);
...trying to create this HOLDING derived column, I cannot get past the "sum function requires 1 argument" nor the "invalid syntax near select keyword" . I need to evaluate this HOLDING sum for each unique customerID
???
thanks in advance